Link to home
Start Free TrialLog in
Avatar of Chris Michalczuk
Chris MichalczukFlag for United Kingdom of Great Britain and Northern Ireland

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].[Renewals]
 where acctid in ('A6UJ9A0006D3','A6UJ9A0006D4')
  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
Avatar of Chris Michalczuk
Chris Michalczuk
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chris Michalczuk

ASKER

this was answered by 2 related questions