CODEWITHSUNDEEP
sqlsql-server-2008t-sql
pencilCake
pencilCake

Reputation: 53323

How can I get a list showing the subtraction result between current Row ID and Previous Row's ID?

How can I get a list of IDs in MS SQL table with a number showing the difference between the current row's ID and the previous row's ID -assuming that I have 'ORDER BY ID DESC'

SELECT ID, ???? AS [CurrentID - PreviousID]
FROM foo
ORDER BY foo.ID DESC

Upvotes: 0

Views: 106

Answers (2)

AjV Jsy
AjV Jsy

Reputation: 6095

SELECT
  ID,
  ID - coalesce(
                 (select max(ID) from foo foo2 where foo2.id<foo.id)
                , 0) as Diff
FROM foo
ORDER BY foo.ID DESC

http://sqlfiddle.com/#!3/1f21eb/3

Upvotes: 0

Kaf
Kaf

Reputation: 33839

Try following query using CTE and row_number():

Fiddle Demo 

create table foo (id int)

insert into foo values 
(1),(5),(8),(9)

;with cte as (
   select Id, row_number() over (order by id desc) rn
   from foo
)
select c1.id, c1.id-c2.id as [currentId - previousId]
from cte c1 
      left join cte c2 on c1.rn = c2.rn - 1
order by c1.rn

| ID | CURRENTID - PREVIOUSID |
-------------------------------
|  9 |                      1 |
|  8 |                      3 |
|  5 |                      4 |
|  1 |                 (null) |

Upvotes: 1

Related Questions