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)