MS SQL 2005: Previous row value

SQL 2005 can number rows by command:
ROW_NUMBER() OVER (ORDER BY field_x,y…)

Another usefull 2205's command allowing "aliasing SQL" is:
WITH table_name AS ( SELECT … ) SELECT table_name ….

Command can be used for creating "virtual" (calcluated) columns


Below, snippet presenting how to archive previous row values in one SQL query.


<br />WITH [TEMP_TABLE] AS <br />( <br /> SELECT <br /> -- Create row number<br /> ROW_NUMBER() OVER (ORDER BY FUND_ID, DATE) AS 'ROW_ID',<br /> FUND_ID,DATE,CASH <br /> FROM CASH_PRO<br />)<br /> <br />-- Join <br />SELECT A.* , B.CASH AS 'PREV_CASH'<br /> FROM [TEMP_TABLE] AS A <br /> LEFT JOIN [TEMP_TABLE] AS B ON B.ROW_ID = (A.ROW_ID -1)<br />