Joining with previous and/or next row

Sometimes in my career I've had to create SQL queries that uses data from the previous and/or next row in the result. That is, something I wont on row X depends on row X-1 or X+1 in the result. Most of the time I have not done this in the SQL query itself but rather in worked on the result but a few times I just had to do it in SQL. This leads to a construction with sub-selects so the resulting query is both hard to read and understand and it is slow due to a large number of sub-selects.

This morning I read a short article describing the use of row_number method introduced in SQL Server 2005. Worth reading if you need to solve similar problem since it removes the need for sub-selects. At least the same amount of sub-selects. To read the article you have to register with the site but it is free and you don't get spammed.