How to select records with minimum price per group

Question

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.

add **cartable.id** into the end of your group by. It's one of those weird things that sql doesn't like — jbutler483, Sep 18 '14 at 15:04
@jbutler483 I wouldn't call this weird, but sensible. In fact, I consider it brain-dead that MySQL doesn't require this. — Frank Schmitt, Sep 18 '14 at 15:05
If you aren't going to group by the ID then why is it in the select list? — Sean Lange, Sep 18 '14 at 15:05
@membersound So you want the lowest price plus the ID belonging to it? What RDBMS are you using? — Frank Schmitt, Sep 18 '14 at 15:05
Because I want to get the ID as result, but I dont want to group it! At the end there might even be more columns that I would like to include into the result set. — membersound, Sep 18 '14 at 15:06
You are not aggregating price or id (min, max sum etc..) and you are not grouping by them because they are not in your group by clause - therefore SQL does know what to do with them. — Dave Sexton, Sep 18 '14 at 15:08
possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Bulat, Sep 18 '14 at 15:34

score 2 · Accepted Answer · answered Sep 18 '14 at 15:06

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;

Bulat · Answer 2 · 2014-09-18T15:38:20.427

2

You can achieve this with EXISTS clause:

SELECT * 
FROM  cartable ct 
WHERE 
  NOT EXISTS (
           SELECT * 
           FROM  cartable 
           WHERE ct.type = type and ct.category = categoery and ct.price < price)

For speed caparison can you try this:

SELECT DISTINCT ON (type, category), id, price
FROM cartable
ORDER BY price DESC

edited Sep 18 '14 at 15:38

answered Sep 18 '14 at 15:24

Bulat

6,869
1
29
52

actually this also works, but in my test has very bad costs/performance in lange datasets. – membersound Sep 18 '14 at 15:27
it does in postgresql http://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by – Bulat Jan 13 '17 at 19:36

score 0 · Answer 3 · answered Sep 18 '14 at 15:08

0

SELECT id, price
from  cartable C
inner join
(
select min(price) as price , category, type
from cartable
group by category, type
)T
on T.category = C.category
and T.type = C.type

answered Sep 18 '14 at 15:08

radar

13,270
2
25
33

score 0 · Answer 4 · answered Sep 29 '20 at 08:43

Most of the time you can't do much else than resolve to use Select - Over

select price, id
from(
    select price, id, [rnk] = ROW_NUMBER() over( partition by category, type order by price)
    from cartable
) as a
where [rnk]=1

Create index appropriately and performance are good.

In your example something like this:

CREATE NONCLUSTERED INDEX [foo]
ON [dbo].[cartable] ([category],[type])
INCLUDE ([price])

score -1 · Answer 5 · answered Nov 15 '20 at 23:07

-1

Maybe you can try:

select id, price from cartable 
where price = (select min(price) from cartable);

answered Nov 15 '20 at 23:07

Elio.b

21
7

How to select records with minimum price per group

5 Answers5