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?

From stackoverflow

• 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.

  Augusto Radtke : That's fine, order is not a issue, I just removed it from the example to make it simpler, I gonna try that.

  MartinStettner : which assumes, that primary keys are generated sequentially and rows are never deleted and the select doesn't have any other order clause and and and ...

  Tom H. : Martin is correct. Although this might work in some cases you really need to define exactly what you mean by "previous" in a business sense, preferably without relying on a generated ID.

  RossFabricant : You're right, I added an improvement using a SQL Server extension.

  Augusto Radtke : That won't run often in the database, and the rows are never deleted. I believe it gonna work fine.

  HLGEM : Remember you might have gaps due to rollbacks.

  JohnFx : In response to "That's fine, order is not an issue"... Then why don't you just subtract an arbitraty value in your query since that is what you are doing if you don't consider order?

• 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.

• Oracle has analytic functions called LAG and LEAD that do this thing.

  In SQL Server, you'll 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.

• 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