I have a table like this:
idAction | numberLine | action
1 | 1 | 1
1 | 2 | 2
1 | 3 | 7
1 | 4 | 6
2 | 1 | 2
1 | 2 | 5
1 | 3 | 3
I need to select for each idAction where the numberLine is MAX the action for that row. So in this example I should get two rows:
idAction | numberLine | action
1 | 4 | 6
2 | 3 | 3
I have tried with Select idAction, MAX(numberLine), action from table Group by idAction
. But it says to put action in the group by, but if I do that it gets me all the rows.
Best Answer
greatest-n-per-group problems are typically solved with a window function:
If you can have more then one maximum value for numberline the above will only return one of them. If you want to see all of them, use
dense_rank()
instead ofrow_number()