taranaki taranaki - 10 days ago 7
SQL Question

Group by on part of composite key

I want to find the cheapest wholesaler for each article only using this table:

Table Article_Wholesaler

article_id wholesaler_id price
1 1 500
1 2 1000
2 1 300


The primary key is composed of the article_id and the wholesaler_id.

Is there any way to achieve this with using the min function?

This query doesn't actually return the correct wholesaler_id, but in order to use this query as a subquery I need both parts of the Primary Key...

select aw.article_id, aw.wholesaler_id, min(aw.price) as min_purchase_price
from Article_Wholesaler aw
group by aw.article_id;


Thank you for any help :).

Answer

You can use a subselect with group by

  select aw.article_id, aw.wholesaler_id, aw.price
  from Article_Wholesaler aw 
  where ( aw.article_id, aw.price in ( select  article_id, min(price)
                                        from Article_Wholesaler
                                         group by article_id )