devxlogo

Access Previous Row Value and Next Row Value

Access Previous Row Value and Next Row Value

There are times that you need to access information from the previous row or next row from the current row. Here is a small trick that makes use of a CTE to get this information:

WITH c AS (SELECTrownum = ROW_NUMBER() OVER (ORDER BY t.ID),t.FirstName,t.SurnameFROM Table t)SELECTp.FirstName PreviousName,c.FirstName CurrentName,n.FirstName NextNameFROM cLEFT JOIN c p ON p.rownum = c.rownum - 1LEFT JOIN c n ON n.rownum = c.rownum + 1GO

devx-admin

Share the Post: