How to find two rows where a field value is x and 2nd row is x + 1

Question

I have a list of records (transactions).

I want to be able to ONLY include rows where the transaction numbers have a sequential value +1. Not even sure where to start to get it to list ONLY those transactions. I have it working to list all transactions sequentially, but not isolate just the transaction values, plus their + 1 transactions. (note that not all transactions values are sequential).

For example,

If field A has values of 1,2,4,7,8,10 I want the script to just list 1,2,7,8 as results.

Thanks in advance.

score 1 · Answer 1 · answered May 13 '15 at 17:31

1

You want groups of at least two consecutive values?

SELECT * FROM tab
QUALIFY 
   MIN(a) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = a - 1
OR MIN(a) OVER (ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) = a + 1

answered May 13 '15 at 17:31

dnoeth

59,503
4
39
56

score 0 · Accepted Answer · answered May 13 '15 at 17:18

0

you can try something like this:

select * from table as t1
where exists (
    select * from table as t2
    where t2.A = t1.A-1 or t2.A = t1.A+1
)
order by t1.A

answered May 13 '15 at 17:18

Iłya Bursov

23,342
4
33
57

Make sure your table doesn't have values like 3,2,1,4,5 – Tim3880 May 14 '15 at 00:48
@Tim3880 why it will be a problem? – Iłya Bursov May 14 '15 at 14:06

How to find two rows where a field value is x and 2nd row is x + 1

2 Answers2