Return only lowest value 2

[lk2fsh]

Three different suppliers supply an identical part. How do I run a query to return only the lowest priced record.

I.E.

Part Number Supplier Price

3 33 $34
3 22 $35
3 12 $29

I would like to return only the record that shows the last row. I know how to do a query, just don't know how to make it select only this lowest value.

Thanks

 

[orna]

SELECT TableParts.*
FROM TableParts WHERE ((TableParts.PartNumber=YourChoice) AND ((TableParts.Price)=(select Min(Price) from TableParts where PartNumber=YourChoice)));

 

[PHV]

Another way:
SELECT TOP 1 * FROM yourTable
WHERE PartNumber=DesiredPart# ORDER BY Price

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

 

[earthandfire]

PHV shouldn't that be:

SELECT TOP 1 * FROM yourTable
WHERE PartNumber=DesiredPart# ORDER BY Price DESC

 

[PHV]

earthandfire, the OP want the LOWEST price.

 

[earthandfire]

Sorry, I've been working all night. I think I should get some sleep.