Search code examples
sqldatabasepostgresqlgreatest-n-per-group

How to select records with minimum price per group

I'd like to select each pair of two columns in a database, but only select the entry with the lowest price. As a result, I want to output the id and the price column.

But it does not work:

My table:

id | category | type | name | price
1;"car";"pkw";"honda";1000.00
2;"car";"pkw";"bmw";2000.00

SQL:

select min(price) price, id
from cartable
group by category, type

Result: Column "cartable.id" must be present in GROUP-BY clause or used in an aggregate function.

Solution

  • If you want the entry with the lowest price, then calculate the lowest price and join the information back in:

    select ct.*
from cartable ct join
     (select category, type, min(price) as price
      from cartable
      group by category, type
     ) ctp
     on ct.category = ctp.category and ct.type = ctp.type and ct.price = ctp.price;