Get value of previous row where results do not have sequential IDs

Question

I have a TSQL table in which the data is the following:

    ID          Value
    3252          2
    93528         3
    2351          5
    1424          19

How can I create an additional column which contains the previous value for a given row? I cannot use the LAG() function as I am using SQL Server 2008.

Example:

    ID          Value       PreviousValue
    3252          2             NULL
    93528         3              2
    2351          5              3
    1424          19             5

I am stuck here as the ID of each row is non-sequential. I believe I have to order the rows somehow, and have made an attempt with the following:

    SELECT RANK() OVER (ORDER BY Value) as Rank, ID, Value, PreviousValue
    FROM MyTable t1
    JOIN MyTable t2 ON t1.id = t2.id
    ORDER BY Value;

Result:

    Rank        ID         Value    PreviousValue
    1       3252          2            2
    2       93528         3            3
    3       2351          5            5
    4       1424          19           9

This question is similar and answers are good https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement — , Aug 31 '17 at 23:04

score 2 · Accepted Answer · answered Aug 31 '17 at 22:47

You can use rank, but you'll need to join on it, so a CTE will be most practical. Also, row_number would be better than rank in case you have duplicate values:

with base(id, value, rank) as (
    select id, value, row_number() over (order by value)
    from   mytable
)
select    t1.id, t1.value, t2.value as prev
from      base t1
left join base t2 on t1.rank - 1 = t2.rank
order by  value;

score 0 · Answer 2 · answered Aug 31 '17 at 22:51

I think this is what you want, just copy/paste to run the sample

declare @sample table(idreal int, other varchar(100))
insert into @sample
select 13, 'a' union
select 1, 'a' union
select 18, 'b' union
select 5, 'd' union
select 4, 'ah'; 


WITH sample2 AS (
  SELECT
    rownum = ROW_NUMBER() OVER (order by idreal),
    idreal,
    other
  FROM @sample
)
SELECT s2.rownum,s2.idreal,s2prev.rownum as previousrnum,s2prev.idreal as             
previousidreal
FROM sample2 s2
LEFT JOIN sample2 s2prev on s2.rownum - 1 = s2prev.rownum

Get value of previous row where results do not have sequential IDs

2 Answers2