Distinct Lowest Query 1

[marcoman44]

I have multiple item names with multiple costs. How do I write a query that will take the lowest cost associated by item name.

For example

Item A 56.34
Item A 34.65
Item B 78.93
Item B 67.45


I want the folowing results:

Item A 34.65
Item B 67.45

Any help is appreciated.

 

[PHV]

SELECT Item, Min(Cost) AS LowerCost
FROM yourTable
GROUP BY Item;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

SELECT t1.item, t1.cost From mytable t1 WHERE t1.cost IN ( SELECT TOP 1 cost From mytable t2 WHERE t1.item=t2.item ORDER BY t2cost ASC)

-DNG