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;