GROUP BY ignoring an attribute

Question

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.

Answer 1

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

Answer 2

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.