CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user2181948
user2181948

Reputation: 1898

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.

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

Upvotes: 1

Views: 656

Answers (2)

Victor Hugo Terceros
Victor Hugo Terceros

Reputation: 3169

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

Upvotes: 0

trincot
trincot

Reputation: 350365

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;

Upvotes: 2

Related Questions