CODEWITHSUNDEEP
sql-serversql-server-2008
Rocky111
Rocky111

Reputation: 255

SQL SELECT Query for "Shifting Row up for one 1 column"

I have table Like This

   value    updatedon   
    ------------------------------              
    1326    2011-10-18 00:06:00.000
    1327    2011-10-18 00:05:00.000
    1330    2011-10-18 00:03:00.000
    1331    2011-10-18 00:02:00.000
    1334    2011-10-18 00:01:00.000
    1333    2011-10-18 00:00:00.000

I want the Result Like this..... Based on the Updated time we can sort.. you can see the table structure its very clear...

 changed        value   updatedon   
    ------------------------------------------              
    1327        1326    2011-10-18 00:06:00.000
    1330        1327    2011-10-18 00:05:00.000
    1331        1330    2011-10-18 00:03:00.000
    1334        1331    2011-10-18 00:02:00.000
    1333        1334    2011-10-18 00:01:00.000
    NULL        1333    2011-10-18 00:00:00.000

Upvotes: 0

Views: 8994

Answers (4)

BazSTR
BazSTR

Reputation: 137

I use postgres for this task. 

select a.value, b.value as shiftedValue, a.updatedon from 
(select ROW_NUMBER() over (order by value,updatedon) as num,* from yourTable) as a
inner join
(select ROW_NUMBER() over (order by value,updatedon) as num,* from yourTable) as b
on a.num = b.num+1

Note, that there is a one null value in first line

Upvotes: 2

Manish
Manish

Reputation: 517

create table #shift(
changed varchar(max),
[value] varchar(max),
updatedon datetime
)

declare @val varchar(max), @value varchar(max);
declare @up datetime, @updatedon datetime;
declare @count int;
set @count=1;

declare shift_cursor cursor for
select [value], updatedon from shift order by updatedon desc
open shift_cursor
fetch next from shift_cursor into @value,@updatedon
while(@@fetch_status=0)
begin
    if(@count=1)
    begin
        set @val=@value;
        set @up=@updatedon;
        set @count=@count+1;
        fetch next from shift_cursor into @value,@updatedon
    end
    insert into #shift values (@value,@val,@up);
    set @val=@value;
    set @up=@updatedon;
    fetch next from shift_cursor into @value,@updatedon 
end
insert into #shift values (null,@value,@updatedon);
select * from #shift;
close shift_cursor
deallocate shift_cursor
truncate table #shift

Upvotes: 1

Siva
Siva

Reputation: 2811

Please check below code

CREATE TABLE Test1 
(
    Col1  INT     ,
    TIME1 DATETIME
);

INSERT  INTO Test1 (Col1, Time1)
VALUES (1, GETDATE()), (2, GETDATE() + 1);

INSERT  INTO Test1 (Col1, Time1)
VALUES (3, GETDATE() + 2), (4, GETDATE() + 4);

SELECT * FROM Test1;

SELECT A.Col1, (SELECT TOP 1 Col1 AS 'Updated By'
FROM   Test1
WHERE  Time1 IN (SELECT MAX(Time1) AS 'Time1'
                 FROM   Test1
                 WHERE  Time1 < A.Time1)) AS 'Updated By' FROM Test1 AS A
ORDER BY Col1 DESC;

Upvotes: 2

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

CREATE TABLE TestData
(
     Value      INT NOT NULL
    ,UpdatedOn  DATETIME NOT NULL
);
INSERT  TestData 
SELECT 1326, '2011-10-18 00:06:00.000'
UNION ALL
SELECT 1327, '2011-10-18 00:05:00.000'
UNION ALL
SELECT 1330, '2011-10-18 00:03:00.000'
UNION ALL
SELECT 1331, '2011-10-18 00:02:00.000'
UNION ALL
SELECT 1334, '2011-10-18 00:01:00.000'
UNION ALL
SELECT 1333, '2011-10-18 00:00:00.000';

SELECT 'Solution 1';
WITH    CteRowNumber
AS
(
SELECT   a.Value
        ,a.UpdatedOn
        ,ROW_NUMBER() OVER(ORDER BY UpdatedOn DESC) RowNumber 
FROM    TestData a
)
SELECT   crt.Value  AS Changed
        ,prev.Value
        ,prev.UpdatedOn
FROM    CteRowNumber prev
LEFT JOIN CteRowNumber crt ON prev.RowNumber + 1 = crt.RowNumber

SELECT 'Solution 2';
DECLARE @Results TABLE
(
     Value      INT NOT NULL
    ,UpdatedOn  DATETIME NOT NULL
    ,RowNumber  INT PRIMARY KEY CLUSTERED
);
INSERT  @Results (Value, UpdatedOn, RowNumber)
SELECT   a.Value
        ,a.UpdatedOn
        ,ROW_NUMBER() OVER(ORDER BY UpdatedOn DESC) RowNumber 
FROM    TestData a;

SELECT   crt.Value  AS Changed
        ,prev.Value
        ,prev.UpdatedOn
FROM    @Results prev
LEFT JOIN @Results crt ON prev.RowNumber + 1 = crt.RowNumber

DROP TABLE TestData;

Upvotes: 3

Related Questions