SQL Select Group By Min() - but select other

Question

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

I need this for Firebird and Filemaker where having min() does not seam to work. I thought this is more generic so I do not have to mention the DBS. — Michael Müller, Jun 10 '17 at 22:56
Possible duplicate of [Firebird Query- Return first row each group](https://stackoverflow.com/questions/37014009/firebird-query-return-first-row-each-group) — Mark Rotteveel, Jun 11 '17 at 08:38
@MichaelMüller but now we do `insert into Products (ID, Product, Price) values (5, 123, 10)` - and what should your select return ? 1, 5 or both ? — Arioch 'The, Jun 13 '17 at 16:18
Does FileMaker has triggers? I think you better make a separate table that would contain proper `ID` and/or `Price` for every `Product`, automatically updated by the RDBMS as you edit the `Products` table. — Arioch 'The, Jun 13 '17 at 16:24

score 4 · Answer 1 · 2017-06-11T13:39:44.853

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;

I am sorry, Its Firebird and Filemaker. – Michael Müller Jun 10 '17 at 23:32 — Michael Müller, Jun 10 '17 at 23:32
@MichaelMüller Firebird 3 supports this syntax. – Mark Rotteveel Jun 11 '17 at 08:35 — Mark Rotteveel, Jun 11 '17 at 08:35
Thanks! For Filemaker "Over" and "Partition" does not work. – Michael Müller Jun 11 '17 at 13:23 — Michael Müller, Jun 11 '17 at 13:23

score 0 · Answer 2 · edited Jun 13 '17 at 16:22

0

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.

edited Jun 13 '17 at 16:22

Arioch 'The

15,799
35
62

answered Jun 10 '17 at 17:58

abwrts

1
1

1

This will not give you correct result as you are calculating only minimum price for all product not for grouped product – Fahad Anjum Jun 10 '17 at 18:05

SQL Select Group By Min() - but select other

2 Answers2