First Value and second last value in SQL

July 25, 2023

I have a table with 4 columns: worker_id,month,basic_salary.
I am trying to write a script that return worker_id, last salary date, second last basic_salary date, last salary amount (from basic_salary column).

Here’s the script i wrote:

with cte as (
select WORKER_ID,  (select max(month) from workers_table) last_salary
,ROW_NUMBER()over (partition by worker_id order by month) rn
from workers_table
group by WORKER_ID,month
)
select * from cte 
where rn=2

here is a sample of the data:

enter image description here

and the data i get is:

WORKER_ID   last_salary rn
2011-11-11  2022-01-04  2
2011-11-12  2022-01-04  2
2011-11-13  2022-01-04  2
2011-11-14  2022-01-04  2
2011-11-15  2022-01-04  2
2011-11-16  2022-01-04  2

The last_salary column is incorrect and i couldn’t find a way to fix it and get the right results.

>Solution :

Try like below. Get your cte & do not use GROUP BY. From cte have self join with condition as first one having rn = 1 and second one having rn = 2. Update SELECT statement. Please check below query.

;WITH cte AS (
    SELECT WORKER_ID,
           month,
           basic_salary,
           ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month) AS rn
    FROM WORKERS_TABLE
)
SELECT c1.WORKER_ID,
       c1.month AS LastSalaryDate,
       c1.basic_salary As Last_basic_salary,
       c2.month AS SecondLastSalaryDate 
FROM cte c1
    JOIN cte c2 ON c1.WORKER_ID = c2.WORKER_ID
WHERE c1.rn = 1 AND c2.rn = 2