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
