How to get difference between two rows for a column field

Question

I have a table like this   rowInt  Value 2       23 3       45 17      10 9       0        The column rowInt values are integer but not in a sequence with same increament  I can use the following sql to list values by rowInt   SELECT   FROM myTable ORDER BY rowInt    This will list values by rowInt  How can get get the difference of Value between two rows with the result like this   rowInt   Value Diff 2        23    22    --45-23 3        45    -35   --10-45 9        0     -45   --0-45 17       10    10    -- 10-0        The table is in SQL 2005  Miscrosoft

User · Answer

SQL Server 2012 and up support LAG   LEAD functions to access the previous or subsequent row  SQL Server 2005 does not support this  in SQL2005 you need a join or something else    A SQL 2012 example on this data     Prepare    select   into  tmp from       select 2  as rowint       23 as Value     union select 3        45     union select 17       10     union select 9        0   x      The SQL 2012 query    select rowInt  Value  LEAD value  over  order by rowInt  - Value   from  tmp   LEAD value  will return the value of the next row in respect to the given order in  over  clause

User · Answer

I d just make a little function for that  Toss in the two values you need to know the difference between and have it subtract the smaller from the larger value  Something like   CREATE FUNCTION  dbo   NumDifference            p1 FLOAT           p2 FLOAT   RETURNS FLOAT AS BEGIN     DECLARE  Diff FLOAT     IF  p1  gt   p2 SET  Diff    p1 -  p2 ELSE SET  Diff    p2 -  p1     RETURN  Diff END   In a query to get the difference between column a and b   SELECT a  b  dbo NumDifference a  b  FROM YourTable

User · Answer

select t1 rowInt t1 Value t2 Value-t1 Value as diff from  select   from myTable  as t1        select   from myTable where rowInt  1       union all select top 1 rowInt COUNT    1 Value 0 from myTable  as t2 where t1 rowInt t2 rowInt-1

User · Answer

If you really want to be sure of orders  use  Row Number    and compare next record of current record  take a close look at  on  clause   T1 ID   1   T2 ID   You are basically joining next row with current row  without specifying  min  or doing  top    If you have a small number of records  other solutions by  Dems  or  Quassanoi  will work fine   with T2 as       select  ID   ROW NUMBER   over  order by rowInt               rowInt  Value     from    myTable   select  T1 RowInt  T1 Value  Diff   IsNull T2 Value  0  - T1 Value from        SELECT  ID   ROW NUMBER   over  order by rowInt                 FROM    myTable   T1         left join T2 on T1 ID   1   T2 ID ORDER BY T1 ID

User · Answer

Query to Find the date difference between 2 rows of a single column   SELECT Column name  DATEDIFF   SELECT MAX date  FROM table name WHERE Column name  lt  b  Column name   Column name  AS days since last FROM table name AS b

User · Answer

Does SQL Server support analytic functions   select   rowint           value           value - lag value  over  order by rowint  diff from     myTable order by rowint

User · Answer

SELECT     current  rowInt      current  Value     ISNULL  next  Value  0  -  current  Value FROM    sourceTable       AS  current  LEFT JOIN    sourceTable       AS  next        ON  next  rowInt    SELECT MIN rowInt  FROM sourceTable WHERE rowInt  gt   current  rowInt    EDIT  Thinking about it  using a subquery in the select  ala Quassnoi s answer  may be more efficient   I would trial different versions  and look at the execution plans to see which would perform best on the size of data set that you have     EDIT2  I still see this garnering votes  though it s unlikely many people still use SQL Server 2005  If you have access to Windowed Functions such as LEAD    then use that instead    SELECT   RowInt    Value    LEAD Value  1  0  OVER  ORDER BY RowInt  - Value FROM   sourceTable

User · Answer

SELECT rowInt  Value         COALESCE                  SELECT TOP 1 Value        FROM myTable mi        WHERE mi rowInt  gt  m rowInt        ORDER BY              rowInt           0  - Value AS diff FROM  myTable m ORDER BY       rowInt

[sql] How to get difference between two rows for a column field?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005