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;
精彩评论