# get only rows with max group value

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)
``````
• How is your present query inefficient? Apr 25, 2017 at 14:09
• This question already has all kinds of answers with different performance on SO: stackoverflow.com/questions/586781/… Nov 3, 2018 at 16:47

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.

• in this case it should return all records as 3 for "b" and 2 for "a" are hights group values for "b" and "a" and no it does not limit to 3 it could return n records, just that those records are in the max value group for specific element Apr 27, 2017 at 10:02