Is there a way to access the previous row value in a SELECT statement

Question

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

User · Answer

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  lt  m1 col1 OR  m2 col1   m1 col1 AND m2 pk  lt  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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Is there a way to access the "previous row" value in a SELECT statement?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008