CODEWITHSUNDEEP
sqlfirebirdgreatest-n-per-groupfilemaker
Michael Müller
Michael Müller

Reputation: 401

SQL Select Group By Min() - but select other

I want to select the ID of the Table Products with the lowest Price Grouped By Product.

ID    Product    Price
1     123        10
2     123        11
3     234        20
4     234        21

Which by logic would look like this:

SELECT
  ID,
  Min(Price)
FROM
  Products
GROUP BY
  Product

But I don't want to select the Price itself, just the ID.

Resulting in

1
3

EDIT: The DBMSes used are Firebird and Filemaker

Upvotes: 2

Views: 3672

Answers (2)

abwrts
abwrts

Reputation: 1

SELECT  ID
FROM  Products as A
where price = ( select Min(Price)
                from Products as B
                where B.Product = A.Product )
GROUP BY id

This will show the ID, which in this case is 3.

Upvotes: 0

user330315
user330315

Reputation:

You didn't specify your DBMS, so this is ANSI standard SQL:

select id
from (
  select id, 
         row_number() over (partition by product order by price) as rn
  from orders
) t
where rn = 1
order by id;

If your DBMS doesn't support window functions, you can do that with joining against a derived table:

select o.id
from orders o
  join ( 
    select product, 
           min(price) as min_price
    from orders
    group by product
  ) t on t.product = o.product and t.min_price = o.price;

Note that this will return a slightly different result then the first solution: if the minimum price for a product occurs more then once, all those IDs will be returned. The first solution will only return one of them. If you don't want that, you need to group again in the outer query:

select min(o.id)
from orders o
  join ( 
    select product, 
           min(price) as min_price
    from orders
    group by product
) t on t.product = o.product and t.min_price = o.price
group by o.product;

Upvotes: 4

Related Questions