Mitch Fincher: The Distracted Programmer: SQL to find non-consecutive numbers in a column

Wednesday, July 28, 2010

We had a need to find non-consecutive numbers in a sequence of numbers. This snippet of SQL finds userIDs which are non-consecutive.

SELECT A.userID + 1
FROM MyTable AS A
WHERE NOT EXISTS (
    SELECT B.userID FROM MyTable AS B 
    WHERE A.userID + 1 = B.userID)
GROUP BY A.userID;

Unknown said...: Excellent. Sometimes you might need to filter using an additional where clauses such as MyColumn=Foo in which case the query should be altered to:

SELECT A.userID + 1
FROM MyTable AS A
WHERE MyCLause AND NOT EXISTS (
SELECT B.userID FROM MyTable AS B
WHERE MyClause AND A.userID + 1 = B.userID)
GROUP BY A.userID;; 2:52 AM
Anonymous said...: Thank you! Exactly what I was looking for.; 4:32 PM