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

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.

  • Database triggers
  • SQL MERGE using GROUP BY
  • Three way join in sql
  • Generating SQL Server DB from XSD
  • How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  • How can I list all foreign keys referencing a given table in SQL Server?
  • 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
    

    2 Solutions collect form web for “Get value of previous row where results do not have sequential IDs”

    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;
    

    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 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.