CODEWITHSUNDEEP
sqldatabase
cwhelms
cwhelms

Reputation: 1771

SQL difference between rows

I have a SQL 2008 database table like this

name      score
-----------------------
steve     207
steve     205
steve     200
steve     139

I want to get the difference between the rows. eqn = [row - (row + 1)] so I would ideally want it to be,

steve 2   //207 - 205
steve 5   //205 - 200
steve 61  //200 - 139
steve 139 //139 - 0

What is the best way to do this? Thanks!

Upvotes: 28

Views: 41196

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

This is one way to do it 

with cte as
(SELECT
   ROW_NUMBER() OVER (PARTITION BY table.name ORDER BY id) row,
   name,
   score
 FROM table)
SELECT 
   a.name ,
   a.score - ISNULL(b.score,0)
FROM
   cte a
   LEFT  JOIN cte b
   on a.name = b.name
    and a.row = b.row+1

Upvotes: 27

Denis de Bernardy
Denis de Bernardy

Reputation: 78561

Another way to do it is using lag():

SELECT
   name,
   score - coalesce(lag(score) over (partition by name order by id), 0)
FROM table
ORDER BY name, id

The lag() function doesn't seem to be available in TSQL but this workaround is suggested in the comments:

select name,
    score - coalesce(
    MIN(score) OVER (PARTITION BY name ORDER BY id
             ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
    , 0) as diff
from table
order by name, id

Upvotes: 16

Related Questions