How to remove all consecutive numbers in select statement?-CodePudding

If I had a SQL Server query that returns numbers in order like this

how can I remove numbers such that no two adjacent pairs are consecutive by 1? The above should be returned like

Is this possible to do?

CodePudding user response：

We can use LEAD and LAG here:

WITH cte AS (
    SELECT id, LAG(id) OVER (ORDER BY id) lag_id, LEAD(id) OVER (ORDER BY id) lead_id
    FROM yourTable
)

SELECT id
FROM cte
WHERE lag_id <> id - 1 OR lead_id <> id   1
ORDER BY id;

CodePudding user response：

You can try to use LEAD and LAG window functions and calculation what rows are consecutive by 1.

SELECT Val
FROM (
 SELECT *,
       LEAD(Val) OVER(ORDER BY Val) - Val gap1,
       Val - LAG(Val) OVER(ORDER BY Val) gap2
 FROM T 
) t1
WHERE gap1 > 1 OR gap2 > 1