Sql server Getting the difference starting from top

Question

Actually what i need was something like this : i have one row with so many columns, and my script should calculate the difference starting from the highest and the second highest, then 2nd highest and 3rd one.. it goes like that. Well Sql server can't even calculate the maximum value in a row ( it can calculate max in one column as i know ) So i used pivot for my table and now i have one column. I ordered them from max to lowest, now what i need is, how will i get the diffence :first value minus second value, second value minus third value...

Answer 1

A couple of ways you can do this - and you should read up sql server analytic and window functions.

Given

DROP TABLE T

CREATE TABLE T (ID INT)
INSERT INTO T VALUES
(1),(10),(2),(5)

You could use the lag analytic function

SELECT ID ,
    LAG(ID, 1,0) OVER (ORDER BY ID DESC) LAGID,
    ID - LAG(ID, 1,0) OVER (ORDER BY ID DESC) DIFF
FROM T 

result

ID          LAGID       DIFF
----------- ----------- -----------
10          0           10
5           10          -5
2           5           -3
1           2           -1

(4 row(s) affected)

or using the row_number() window function

SELECT  TID,TRN,UID,URN, TID - UID AS DIFF
FROM
(
SELECT  T.ID TID 
        ,ROW_NUMBER() OVER (ORDER BY T.ID DESC) TRN
FROM T
) S
LEFT JOIN 
(SELECT  ID UID 
        ,ROW_NUMBER() OVER (ORDER BY ID DESC) URN
FROM T
) U
ON URN = TRN - 1

result

TID         TRN                  UID         URN                  DIFF
----------- -------------------- ----------- -------------------- -----------
10          1                    NULL        NULL                 NULL
5           2                    10          1                    -5
2           3                    5           2                    -3
1           4                    2           3                    -1

(4 row(s) affected)