Sql – way to access the “previous row” value in a SELECT statement


I need to calculate the difference of a column between two lines of a table. Is there any way I can do this directly in SQL? I'm using Microsoft SQL Server 2008.

I'm looking for something like this:

SELECT value - (previous.value) FROM table

Imagining that the "previous" variable reference the latest selected row. Of course with a select like that I will end up with n-1 rows selected in a table with n rows, that's not a probably, actually is exactly what I need.

Is that possible in some way?

Best Solution

Use the lag function:

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

Sequences used for Ids can skip values, so Id-1 does not always work.