get only rows with max group value

Question

for example, we have:

element | group_value | value
a       | 1           | 2000
a       | 2           | 1500
a       | 2           | 2500
b       | 1           | 1000

I'd like to return only last 3 records as those are the records with max group value for each element.

I know for a solution with subquery but is there an efficient one?

To clarify: for element 'a':

2 is highest group_value so it returns rows 2 and 3 > (and not the first row as it's group value is not highest),

for element 'b':

1 is > highest group_value so it returns row(s) 4

My (performance-vice not good) solution to the problem is:

select * 
from   x x1 
where (element, group_value) in (select   element, max(group_value) 
                                 from     x x2 
                                 where    x1.element = x2.element 
                                 group by x2.element)

Answer 1

First answer uses a CTE to select max(group_value) and then join with the table.

with maxgv as 
(
    select element, max(group_value) maxg
    from x
    group by element
)
select x.element, x.group_value, x.value
from   maxgv
inner join x
on         x.element = maxgv.element
and        x.group_value = maxgv.maxg
;

This one uses RANK() function:

with grp as 
 (
     select element, group_value, value,
            rank() over (partition by element order by element, group_value desc) rn
     from x
)
select element, group_value, value
from   grp
where  rn = 1;

Both returns the same result:

| element | group_value | value |
|---------|-------------|-------|
| a       | 2           | 1500  |
| a       | 2           | 2500  |
| b       | 1           | 1000  |

Rextester here

_{But, just to check and compare performance, we need the table schema. I don't know which index is using your current query.}