CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Sharmil
Sharmil

Reputation: 105

Difference in consecutive rows in SQL

I have a table which has any integer number. There is no specific criteria for a number to start but next row will be +2000 in number then above row and so on. So I want to find out through query where the difference of 2 rows are not 2000. Could you please help me on this? Comparison would be as follows: 

Row 1 = 1000
2 =     3000
3       4000
4=      6000
5=       7000

Then only 3 and 5 should be output as the difference of Row 3 and Row 5 is not 2000. Row 3 should be compared with 2 and 5 should be compared with 4.

My data looks like :

Label Formorder date

test 480000 3/31/2015 test2 481000 3/31/2014 test3 482000 3/31/2015 test4 483000 3/31/2014

enter image description here

Upvotes: 3

Views: 2087

Answers (4)

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

A JOIN-based approach should work on SQL Server 2008.

In the query below, row numbers are added to the source data. Then, an inner join connects the current row to the previous row if and only if the previous row's value is not exactly 2000 less than the current row. 

WITH Data AS (
  SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY rowValue)
  FROM   dataTable
)

SELECT n.rowValue
FROM data n
  JOIN data p ON p.RowNumber = n.RowNumber - 1
    AND p.rowValue != n.rowValue - 2000

http://sqlfiddle.com/#!3/59d28/10

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

If ID (row) does not skip 

select t2.*  
 from table t1 
 jion table t2 
   on t2.ID = t1.ID + 1 
  and t2.value <> t1.value + 2000

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Possible solution:

declare @t table(id int, v int)

insert into @t values
(1, 1000),
(2, 3000),
(4, 7000),
(6, 9000),
(11, 11000),
(17, 17000)


select * from @t t1
outer apply(select * from (
                select top 1 id as previd, v as prevv 
                from @t t2 
                where t2.id < t1.id 
                order by id desc)t 
            where t1.v - t.prevv <> 2000) oa
where oa.prevv is not null

Output:

id  v       previd  prevv
4   7000    2      3000
17  17000   11     11000

Upvotes: 0

paul
paul

Reputation: 22021

If you have SQL Server 2012 or above, you can use the LAG function.

LAG will give you a value in the previous row, compare this value to see if it is 2000 lower than the current row:

WITH diffs as (
    SELECT rowValue,
           rowValue - LAG(rowValue) OVER (ORDER BY rowValue) diff
    FROM   dataTable)
SELECT rowValue
FROM   diffs
WHERE  diff <> 2000

http://sqlfiddle.com/#!6/59d28/2

Upvotes: 1

Related Questions