View to show lowest value only

6 Apr 2006 10:45 PM

Mike

I have a table that contains product prices. The problem is that sometimes
it will have two prices for the same product. For example:

Part    Price
1        10
2        25
2        35
3        5

I would like to create a view that would display only the lowest price.
Sample outout would be:

Part    Price
1        10
2        25
3        5

Any ideas?

Thanks,
Mike

6 Apr 2006 10:50 PM

David Portas

Show quote

"Mike" <mba***@yahoo.com> wrote in message
news:eDJyevcWGHA.1192@TK2MSFTNGP04.phx.gbl...
>I have a table that contains product prices. The problem is that sometimes
> it will have two prices for the same product. For example:
>
> Part    Price
> 1        10
> 2        25
> 2        35
> 3        5
>
> I would like to create a view that would display only the lowest price.
> Sample outout would be:
>
> Part    Price
> 1        10
> 2        25
> 3        5
>
> Any ideas?
>
> Thanks,
> Mike
>
>

SELECT part, price
FROM your_table AS T
WHERE price =
(SELECT MIN(price)
FROM your_table
WHERE part = T.part) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

6 Apr 2006 10:53 PM

Alejandro Mesa

Mike,

Use the "group by" clause and the aggregation function "min".

select part, min(price) as min_price
from your_table
group by part
go

AMB

Show quote

"Mike" wrote:

> I have a table that contains product prices. The problem is that sometimes
> it will have two prices for the same product. For example:
>
> Part    Price
> 1        10
> 2        25
> 2        35
> 3        5
>
> I would like to create a view that would display only the lowest price.
> Sample outout would be:
>
> Part    Price
> 1        10
> 2        25
> 3        5
>
> Any ideas?
>
> Thanks,
> Mike
>
>
>

6 Apr 2006 11:14 PM

Mike

Thanks, the group by code worked!

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:B9F00682-0677-4056-AF4D-CE97799C7BC3@microsoft.com...
> Mike,
>
> Use the "group by" clause and the aggregation function "min".
>
> select part, min(price) as min_price
> from your_table
> group by part
> go
>
>
> AMB
>
> "Mike" wrote:
>
> > I have a table that contains product prices. The problem is that
sometimes
> > it will have two prices for the same product. For example:
> >
> > Part    Price
> > 1        10
> > 2        25
> > 2        35
> > 3        5
> >
> > I would like to create a view that would display only the lowest price.
> > Sample outout would be:
> >
> > Part    Price
> > 1        10
> > 2        25
> > 3        5
> >
> > Any ideas?
> >
> > Thanks,
> > Mike
> >
> >
> >