Reputation: 53323
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
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
Reputation: 33839
Try following query using CTE
and row_number()
:
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