GROUP BY ignoring an attribute
for example I have this table:
itemgroup | description | price
A, a, 10
A, b, 12
A, c, 14开发者_运维知识库
B, g, 11
B, h, 16
I want to select the rows with the highest price in one group like this:
A, c, 14
B, h, 16
The SQL query (is fully functional) which gets me near this is:
SELECT itemgroup, MAX( price )
FROM table
GROUP BY itemgroup
A, 14
B, 16
By trying this I get an "not a GROUP BY expression"-error:
SELECT itemgroup, description, MAX( price )
FROM table
GROUP BY itemgroup
I need something like this pseudo query:
SELECT itemgroup, IGNORE( description), MAX( price )
FROM table
GROUP BY itemgroup
I hope I could explain my little problem.
I normally end up doing something like:
SELECT t1.itemgroup, t1.description, t1.price
FROM table t1,
(SELECT itemgroup, MAX( price ) as price
FROM table
GROUP BY itemgroup) t2
WHERE t1.itemgroup = t2.itemgroup
AND t1.price = t2.price
Use the analytic functions:
SELECT itemgroup, description, price FROM
(select itemgroup, description, price, RANK()
OVER (PARTITION BY itemgroup ORDER BY max(price) DESC) as rank
FROM group by itemgroup,description,price)a
WHERE a.rank = 1
ORDER BY itemgroup;
There's a lot of power in the analytic functions - learning them can help you in a lot of situations.
精彩评论