Edwin Jarvis Edwin Jarvis - 4 months ago 8
SQL Question

There is a 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, there is 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 that with a select like this 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?

Answer

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

If you need to break ties, you can add as many columns as necessary to the ORDER BY.