Ms-access – How to shift a value one record ‘down’

ms access

Suppose we have a field ("Flag"). How we can shift records of this flag in another field ("ShiftField") by using SQL code?

See the example:

ID   Flag     ShiftFlag
1    0  
2    1        0
3    1        1
4    1        1
5    0        1
6    0        0
7    1        0
8    1        1
9    1        1
10   0        1

My efforts already was something like below:

for i=1 to n str = 
  "UPDATE HoboData Set ShiftFlag = Wet_Estimated80 WHERE ID = " & i + 1 
next i 
Dim cmd As OleDbCommand = New OleDbCommand(str, Connect)

but this way is very slow

Best Answer

This will do the trick in recent versions of SQL Server and Oracle:

SELECT ID, Flag,
  LAG(Flag) OVER(ORDER BY id) AS ShiftFlag
FROM tab
ORDER BY id;

If you don't have LAG(), for example in MS SQL Server 2008, you can use ROW_NUMBER() like this:

;WITH num AS (
  SELECT ID, Flag, ROW_NUMBER() OVER(ORDER BY id) AS n FROM tab
)
SELECT t1.ID, t1.Flag, t2.Flag AS ShiftFlag
FROM num t1
LEFT OUTER JOIN num t2 ON t2.n = t1.n - 1
ORDER BY t1.ID;

Tested on SQLFiddle.

Related Question