Self Join

Hi All,

I have following table.

Row Batch_Suffix
1 710001
4 710002
6 710003
10 710004
15 710005
16 710006
20 710007
24 710011
27 710012
30 710013
33 710014
36 710015
40 710016

I need to add the difference column as follows.

Row Batch_Suffix RowDifference
1 710001 3
4 710002 5
6 710003 9
10 710004 14
15 710005 15
16 710006 19
20 710007 23
24 710011 26
27 710012 29
30 710013 32
33 710014 35
36 710015 39
40 710016  

Many thanks in advance.

Ali

July 26th, 2015 2:51am

please post which edition of sql you are using??

your row difference is not really row difference but next row's number minus one.

declare @table table (sno int,sno2 int)

insert into @table
values (1,72001),(4,72003),(6,72005),(10,72010),(15,72015)

--works in any version of SQL
;with CTE as (select row_number() over (order by sno) as R1,sno,sno2 from @table)

select A.sno,A.sno2,B.sno-1 as [RowDifference] from CTE A Left Outer Join CTE  B on A.R1+1=B.R1

--works in sql 2012 and above
select *,lead(sno) over(order by sno)-1 as [RowDifference] from @table


Free Windows Admin Tool Kit Click here and download it now
July 26th, 2015 3:04am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics