Ho to assign Previous value in column for each record

Question

I have one table scenario in which data looks like this .

Request Id  Field Id    Current Key
1213         11          1001
1213         12          1002
1213         12          103
1214         13          799
1214         13          899
1214         13           7

In this when loop starts for first Request ID then it should check all the field ID for that particular request ID. then data should be look like this .

Request Id  Field Id    Previous Key  Current Key
1213         11          null            1001
1213         12         null             1002
1213         12         1002             103
1214         13         null             799
1214         13         799              899
1214         13         899               7

When very first record for Field id for particular request id come then for it should be take null values in Previous key column and the current key will remain the same.

When the second record will come for same field ID its should take previous value of first record in Previous key column and when third record come it should take previous value of second record in Previous column and so on .

When the new field ID came the same thing should be repeated again.

Please let me know if you need any more info.Much needed your help.

Brino · Answer 1 · 2015-05-13T15:57:14.763

IN SQL 2008 You do not have the benefit of the lead and lag functions. Instead you must do a query for the new column. Make sure you query both tables in the same order, and add a row_num column. Then select the greatest row_num that is not equal to the current row_num and has the same request_id and field_id.

select a.request_id,
       a.field_id,
       (select x.current_key
          from (select * from (select t.*, RowNumber() as row_num from your_table t) order by row_num desc) x
         where x.request_id = a.request_id
           and x.field_id = a.field_id
           and x.row_num < a.row_num
           and RowNumber()= 1
           ) as previous_key,
       a.current_key
  from (select t.*, RowNumber()as row_num from your_table t) a

IN SQL 2012+ You can use the LAG or LEAD functions with the OVER clause to get the previous or next nth row value:

select 
Request_Id, 
Field_Id,   
lag(Current_Key,1) over (partition by Request_ID, Field_ID) as Previous_Key  
,Current_Key
from your table

You should probably look at how you order your results too. If you have multiple results lag will only grab the next row in the default order of the table. If you had another column to order by such as a date time you could do the following:

lag(Current_Key,1) over (partition by Request_ID, Field_ID order by timestampColumn)

@Ajay2707 thanks for noticing that. I have updated the answer. Makes me really appreciate analytic functions and windowing. — Brino, May 13 '15 at 15:58

score 0 · Answer 2 · edited May 23 '17 at 10:26

You can check this.

Declare @t table (Request_Id int,  Field_Id int, Current_Key int)

insert into @t values (1213, 11, 1001),(1213, 12, 1002), (1213, 12, 103) , (1214, 13, 799),  (1214, 13, 899), (1214, 13, 7)

;with cte
as (
select 0 rowno,0 Request_Id, 0 Field_Id, 0 Current_Key
union
select ROW_NUMBER() over(order by request_id) rowno, * from @t
)

select  
    t1.Request_Id , t1.Field_Id ,   
    case when t1.Request_Id = t2.Request_Id and t1.Field_Id = t2.Field_Id 
    then t2.Current_Key 
    else null
    end previous_key
    , t1.Current_Key 
    from cte t1, cte t2 
where t1.rowno = t2.rowno + 1

Refer link when you want to compare row value

score 0 · Answer 3 · answered May 13 '15 at 14:59

When the second record will come for same field ID...

Tables don't work this way: there is no way to tell that 1213,12,1002 is the "previous" record of 1213,12,103 as you assume in your example.

Do you have any data you can use to sort your records properly? Request id isn't enough because, even if you guarantee that it increments monotonically for each operation, each operation can include multiple values for the same item id which need to be sorted relative to each other.

score 0 · Answer 4 · answered May 15 '15 at 07:35

try this,

 declare @tb table (RequestId int,FieldId int,  CurrentKey int)
    insert into @tb (RequestId,FieldId,CurrentKey) values
    (1213,11,1001),
    (1213,12,1002),
    (1213,12,103),
    (1214,13,799),
    (1214,13,899),
    (1214,13, 7)
    select RequestId,t.FieldId,
case when t.FieldId=t1.FieldId then t1.CurrentKey end as PreviousKey,t.CurrentKey from 
    (select *, ROW_NUMBER() over (order by RequestId,FieldId) as rno 
from @tb) t left join
    (select FieldId,CurrentKey, 
ROW_NUMBER() over (order by RequestId,FieldId) as rno from @tb) t1 on t.rno=t1.rno+1

Ho to assign Previous value in column for each record

4 Answers4