sql gap missing values 1

sql gap missing values

SELECT * FROM (
  SELECT x, (SELECT min(x) FROM my_table t2 WHERE t2.x > t.x) next_x
  FROM my_table t
) WHERE x <> next_x - 1;
/* if my_table contains for x:  1 2 5 6 7 10
    X		NEXT_X
    2		5
    7		10
*/
-- ⇓ Test it ⇓ (Fiddle source link)

Here is what the above code is Doing:
1. The inner query is selecting all the values of x and the next value of x.
2. The outer query is selecting only the rows where the value of x is not equal to the next value of x minus 1.

Similar Posts