Get previous row in a table

1

As an example I have a table and I can use the max function to return the most recent dates for whats changed which works fine but I want to return also the previous date to the maximum

See pic attached

image
image923×649 20.4 KB

Whats the best way to do this ?

cheers

2

There are a few different ways I have seen people do this. One of my favorites is the row_number function as in the code below:

;WITH cte AS
(
    SELECT
        t.*,
        RN = ROW_NUMBER() OVER(ORDER BY t.[when] DESC)
    FROM
        table1 AS t
)
SELECT *
FROM
    cte AS c
WHERE
    RN = 2;

If there can be ties, i.e., two rows with the latest value in the when column, use dense_rank instead of row_number.

3

hi

hope this helps

create data script

declare @Temp table (ID int ,FullName varchar(20) , whatschanged varchar(20), when_date date ,changedfrom varchar(20) , changedto varchar(20) )

insert into @Temp values
(1,'Fred Jones','Employer Change','2021-10-27','Truck Ltd','Bus Ltd')
,(1,'Fred Jones','Employer Change','2021-10-15','Train Ltd','Truck Ltd')
,(1,'Fred Jones','Job Change','2022-08-30','Labourer','Supervisor')
,(1,'Fred Jones','Job Change','2021-10-30','Clerk','Labourer')

select 
    top 1 with ties * 
from 
    @temp 
order by 
   row_number() over (partition by id,FullName,whatschanged order by when_date )

image
image1048×330 15.3 KB

4

Thanks a lot but it doesnt work in this situation

eg1
eg1986×467 14.6 KB

5

please explain clearly

in this situation

how you are looking for
2022-07-20 employer change

6

In the original post - you said you wanted the latest and the previous row. It seems now you only want the previous row - or the latest row if no previous row exists.

So which is it? Just the previous row - or both the latest and previous? And now you have included a new column - createdon - instead of 'When'.

Either way, use a derived table (or CTE) to assign a row number in reverse order. Then select from that and use another row_number in ascending order. Then select from that where that row number = 1.

   WITH LatestRows
     AS (
 SELECT *
      , rn = row_number() OVER(PARTITION BY t1.whatschanged ORDER BY t1.When DESC)
   FROM table1 AS t1
  WHERE t1.id = 1
        )
      , PreviousRows
     AS (
 SELECT *
      , rn = row_number() OVER(PARTITION BY lr.whatschanged ORDER BY lr.When ASC)
   FROM LatestRows AS lr
  WHERE lr.rn <= 2
        )
 SELECT *
   FROM PreviousRows AS pr
  WHERE pr.rn = 1;

If you want to do this for more than a single ID - then adjust the partition to include the ID.