Select to get a value from a id combined with a max value

Question

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.

score 1 · Accepted Answer · answered Oct 31 '16 at 10:49

greatest-n-per-group problems are typically solved with a window function:

select idAction, numberLine, action
from (
  select idAction, numberLine, action, 
         row_number() over (partition by idaction order by numberline desc) as rn 
  from the_table
) t
where rn = 1;

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 of row_number()

Select to get a value from a id combined with a max value

1 Answers1