Wednesday, July 28, 2010

SQL to find non-consecutive numbers in a column

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;
at

2 comments:

  1. Unknown2:52 AM

    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;

    ReplyDelete
  2. Anonymous4:32 PM

    Thank you! Exactly what I was looking for.

    ReplyDelete