Query group by and display other columns

[VDJ]

Hi all,

I have a table with 4 columns :
Product Price Quantity Supplier

Product1 5 240 A
Product1 7 19 B
Product1 6 12 C
Product2 96 0 A
Product2 98 23 B
Product2 99 44 C

There are 3 suppliers for the products (name of the suppliers are A, B and C).
I want to make a query with the following result :
Product1 5 240 A
Product2 98 23 B

In other words :
Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).
I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.

Thanks in advance.

Johan

 

[CJ_London]

Not tested but try:

SELECT * 
FROM myTable 
WHERE Price=(SELECT Min(Price) FROM myTable as tmp WHERE Product=myTable.Product AND Quantity<>0)

You'll need to change names to suit

 

[VDJ]

Thank you very much, it works from the first time !

I appreciate your help very much.

Johan.