taranaki taranaki - 1 year ago 79
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 Source

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 )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download