Ask Your Question

Query, Find missing numbers [closed]

asked 2017-07-31 21:27:29 +0100

Joe Castor gravatar image

I'm trying to produce a list of missing numbers from the ID sequence of a table. I've been searching for ideas with little luck and have attempted the following. I can't seem to even get past the syntax error level. Could that be quickly reviewed to see if I'm even in the ballpark> Thank you in advance, Joe -- Object is to find missing numbers in column (MemberID) -- in a range from 1 to 246 (range not in yet??) From MemberMaster table SELECT "MemberID" FROM "MemberMaster" CASEWHEN MAX("MemberID") = COUNT() THEN MAX("MemberID") + 1 -- only goes to 1 higher than biggest in the list foe now WHEN MIN("MemberID") > 1 THEN 1 WHEN MAX("MemberID") <> COUNT() THEN (SELECT MIN("MemberID")+1 FROM "MemberMaster" WHERE ("MemberID" + 1) NOT IN (SELECT "MemberID" FROM "MemberMaster")) ELSE NULL END FROM "MemberMaster"

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-07-31 23:54:08.773694

1 Answer

Sort by » oldest newest most voted

answered 2017-07-31 22:46:48 +0100

Ratslinger gravatar image

This should give you a usable list:

SELECT (t1."MemberID" + 1) as gap_starts_at, 
       (SELECT MIN(t3."MemberID") -1 FROM "MemberMaster" t3 WHERE t3."MemberID" > t1."MemberID") as gap_ends_at
FROM "MemberMaster" t1
WHERE NOT EXISTS (SELECT t2."MemberID" FROM "MemberMaster" t2 WHERE t2."MemberID" = t1."MemberID" + 1)
HAVING gap_ends_at IS NOT NULL

Based on info from this post.

edit flag offensive delete link more


Thank you. Not quite what I was going for, but this works.

Joe Castor gravatar imageJoe Castor ( 2017-07-31 23:53:10 +0100 )edit

Question Tools

1 follower


Asked: 2017-07-31 21:27:29 +0100

Seen: 332 times

Last updated: Jul 31 '17