[sql] Is there 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. 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?

select t2.col from (
select col,MAX(ID) id from 
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2

Oracle, PostgreSQL, SQL Server and many more RDBMS engines have analytic functions called LAG and LEAD that do this very thing.

In SQL Server prior to 2012 you'd need to do the following:

SELECT  value - (
        SELECT  TOP 1 value
        FROM    mytable m2
        WHERE   m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
        ORDER BY 
                col1, pk
        )
FROM mytable m1
ORDER BY
      col1, pk

, where COL1 is the column you are ordering by.

Having an index on (COL1, PK) will greatly improve this query.

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.

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
    value
  FROM table
)
SELECT
  curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1

LEFT JOIN the table to itself, with the join condition worked out so the row matched in the joined version of the table is one row previous, for your particular definition of "previous".

Update: At first I was thinking you would want to keep all rows, with NULLs for the condition where there was no previous row. Reading it again you just want that rows culled, so you should an inner join rather than a left join.

Update:

Newer versions of Sql Server also have the LAG and LEAD Windowing functions that can be used for this, too.

The selected answer will only work if there are no gaps in the sequence. However if you are using an autogenerated id, there are likely to be gaps in the sequence due to inserts that were rolled back.

This method should work if you have gaps

declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by  primarykey

select t1.value - t2.value from @temp  t1
join @temp  t2 
on t1.tempid = t2.tempid - 1