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

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

CodePudding user response：

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;

CodePudding user response：

For versions prior to 8.0, a correlated subquery can be used to do the trick.

select * From `table` t where not exists
(select id,max(`month`) mx from `table` group by id having t.id=id and t.`month`=mx) 
;