CODEWITHSUNDEEP
mysqlselectgroup-by
spiderplant0
spiderplant0

Reputation: 3952

Use group by in and return identity of the row

For this table...

id  type   food     price 
--------------------------
1   veg    carrot   10    
2   veg    turnip   11
3   fruit  bramble  6
4   fruit  rasp     4
5   fruit  current  9
...

I can return the max price of the most expensive food for each food type like this...

select max(price) from tableName group by type;

But I'd like to return the id number of each row that contains the most expensive food for each food type. And return one and only one row per food type. Ie return this....

id  
----
2
5 
...

This is a simplified version of my real problem.

Upvotes: 0

Views: 447

Answers (3)

Oceanic
Oceanic

Reputation: 1458

SELECT max(id) from
(SELECT t1.* FROM tableName t1 
JOIN (SELECT type, max(price) AS price FROM tableName GROUP BY type) t2 
ON t2.type = t1.type AND t2.price = t1.price) t3
GROUP BY userId;

Upvotes: 0

Vili
Vili

Reputation: 1679

This horrible query will work when more food have the same type and price.
I would hardly ever use this in production as this is unmaintainable.

SELECT MIN(id) AS id 
FROM (
    SELECT t.id AS id, t.type
    FROM tableName t
    JOIN (
        SELECT MAX(price) AS m, type
        FROM tableName 
        GROUP BY type
    ) sub 
        ON sub.m=t.price AND sub.type=t.type
    ORDER BY id
) whatever
GROUP BY type

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT id
FROM ( SELECT id, type
       FROM table
       ORDER BY price DESC) AS h
GROUP BY type

Upvotes: 1

Related Questions