Search code examples
mysqlwindowrow-number

how to exclude last row in every group by id in mysql?

the 3 columns are employee_id, month,salary. How to exclude last row in every employee_id group?

[1, 1, 20] 
[1, 2, 30] 
[1, 3, 40] 
[1, 4, 60] 
[1, 7, 90] 
[1, 8, 90]  #exclude

[2, 1, 20] 
[2, 2, 30] #exclude

[3, 2, 40] 
[3, 3, 60] 
[3, 4, 70] #exclude

My query adds rownumber, but then what can i do?

select 
id, month, salary, 
row_number()over(partition by id order by id) as ro #i tried this ,but how?


from employee 
order by id, month
Solution

  • You can use order by month DESC in row number and then exclude 1 which corresponds to the most recent month for that id.

    select id, month, salary 
from (
select 
id, month, salary, 
row_number()over(partition by id order by month desc) as ro 
from employee ) e
where ro > 1
order by id, month;