February 26, 2014 at 4:06 am
Hi,
I'm trying to compare rows to find gaps:
create table dird (colA int)
insert into dird values (4)
insert into dird values (7)
insert into dird values (14)
The idea is that you subtract row 2 from 1. e.g.:
Value Difference
----- -----------
7 3
14 7
My attempt is:
WITH T1 AS
(SELECT Row_Number() OVER(ORDER BY colA) N,
colA from dird)
SELECT a.colA, a.N, b.colA-a.colA FROM T1 a
left JOIN T1 AS b
ON a.colA = b.colA-1
I keep getting null for table B, any idea what I'm doing wrong?
February 26, 2014 at 4:13 am
Hi,
You're joining on ColA rather than N (the rownr).
Try;
;WITH T1 AS
(SELECT Row_Number() OVER(ORDER BY colA) N,
colA from dird)
SELECT a.colA, a.N, a.colA-b.colA FROM T1 a
left JOIN T1 AS b
ON a.N-1 = b.N
(I've switched round the difference calc as it was producing negatives the way round you originally had it)
February 26, 2014 at 4:46 am
Ah I see haha, I guess that's why the difference is null (since the gaps).
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply