开发者

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 typ开发者_开发知识库e. 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.


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


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


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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜