Chris Michalczuk
asked on
LAG_ROWID - how do I get the right order using this query?
How to I change this to get the right result in LAG_ROWID see what I got and what i expected
Note the last in the series always has to be NULL too !
SELECT ROWID, AcctID
, ROW_NUMBER() OVER ( PARTITION BY AcctID ORDER BY AccountOrder ) AccountOrder
, LAG(ROWID) OVER ( PARTITION BY AcctID ORDER BY AccountOrder) LAG_ROWID
FROM [OrbisSales].[dbo].[Renewa ls]
where acctid in ('A6UJ9A0006D3','A6UJ9A000 6D4')
order by rowid
this is the result I got but is the wrong order
ROWID AcctID AccountOrder LAG_ROWID I expected Lag_rowid to return
1 A6UJ9A0006D3 1 NULL 2
2 A6UJ9A0006D3 2 1 3
3 A6UJ9A0006D3 3 2 4
4 A6UJ9A0006D3 4 3 NULL
7 A6UJ9A0006D4 1 NULL 8
8 A6UJ9A0006D4 2 7 9
9 A6UJ9A0006D4 3 8 NULL
Note the last in the series always has to be NULL too !
SELECT ROWID, AcctID
, ROW_NUMBER() OVER ( PARTITION BY AcctID ORDER BY AccountOrder ) AccountOrder
, LAG(ROWID) OVER ( PARTITION BY AcctID ORDER BY AccountOrder) LAG_ROWID
FROM [OrbisSales].[dbo].[Renewa
where acctid in ('A6UJ9A0006D3','A6UJ9A000
order by rowid
this is the result I got but is the wrong order
ROWID AcctID AccountOrder LAG_ROWID I expected Lag_rowid to return
1 A6UJ9A0006D3 1 NULL 2
2 A6UJ9A0006D3 2 1 3
3 A6UJ9A0006D3 3 2 4
4 A6UJ9A0006D3 4 3 NULL
7 A6UJ9A0006D4 1 NULL 8
8 A6UJ9A0006D4 2 7 9
9 A6UJ9A0006D4 3 8 NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER