Find all rows where there is another row which has in same field value from to-be-found-row +1

Question

I have the following table (irrelevant fields and rows emitted for clarity):

customerID        MediaIDdec
--------------    ----------------------
.                 .
.                 .
.                 .
16253             453456691
36178             453456692
24352             671254112
81432             226124312
44513             226124313
31336             226124314
64231             453653811
.                 .
.                 .
.                 .

The query should return all rows (row1) where MediaIDdec within another row (row2) is MediaIDdec (Row1) + 1 .

From the above example table, this would return:

16253             453456691     (because there is MediaIDdec+1 within row with customerID 36178)
81432             226124312     (because there is MediaIDdec+1 within row with customerID 44513)
44513             226124313     (because there is MediaIDdec+1 within row with customerID 31336)

My SQL skills are honestly not sufficient to solve such a query.

Hint: The table is sorted after MediaIDdec.

Your help is greatly appreciated.

Answer 1

You could use exists:

select t.*
from mytable t
where exists (select 1 from mytable t1 where t1.MediaIDdec = t.MediaIDdec + 1)

If MediaIDdec has no duplicates, an alternative is lead():

select *
from (
    select t.*, lead(MediaIDdec) over(order by MediaIDdec) leadMediaIDdec 
    from mytable t
) t
where leadMediaIDdec = MediaIDdec + 1