开发者

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.

0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜