SQL query for difference in rows in a column

I am struggling to write a query for the following task:

 X   Y                                       X    Y    Seq    Difference 
---------                                  ------------------------------
 20   35                                     20   35   1        35  (45 - 0)
 21   45           ------>                   21   45   1        45 (35-0)
 21   52                                     21   52   2        7 (52-45)
 22   66                                     22   66   1        66 (66-0) 
 22   68                                     22   68   2        2 (68-66)
 22   77                                     22   77   3        9 (77 - 68)

The table on the left is given wh开发者_StackOverflow社区ere column X is sorted. The table on the right is what I am trying to generate with two extra clumns Seq and Difference. Seq counts the number of unique members in X and assigns a sequential value (in the example above there are one 20 , two 21s and three 22s) . The difference column takes the differences of consecutive rows of each UNIQUE X.

Your help is much much appreciated.

thanks

Can't do this with a simple query as each row cannot access the contents of the others, but you could easily use a cursor and build a temp table with this data. Something like

DECLARE db_cursor CURSOR FOR  
select x, y from tablename

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @x, @y

WHILE @@FETCH_STATUS = 0   
BEGIN   
    -- Do your math and inserts here  
    FETCH NEXT FROM db_cursor INTO @x, @y
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

You could also use a subquery. It generates a temporary table in memory as well that can be used in the final result set.

Something like this should work:

create table leftSide
(
X int,
Y int
)
insert into leftSide select 20, 35
insert into leftSide select 21, 45
insert into leftSide select 21, 52
insert into leftSide select 22, 66
insert into leftSide select 22, 68
insert into leftSide select 22, 77

;WITH MyCte (X, Y, Seq)
AS
(
select X,Y,
       ROW_NUMBER() OVER(PARTITION BY X ORDER BY X) AS Seq
from leftSide
)
select a.*, Diff = (a.Y - coalesce(b.Y,0))
from MyCte a
left join MyCte b
on a.X = b.X
    and a.Seq = b.Seq + 1