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 (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY t.ID),
t.FirstName,
t.Surname
FROM Table t
)

SELECT
p.FirstName PreviousName,
c.FirstName CurrentName,
n.FirstName NextName
FROM c

LEFT JOIN c p ON p.rownum = c.rownum - 1
LEFT JOIN c n ON n.rownum = c.rownum + 1
GO