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
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;