asked on

Find the missing number

Being unexperienced in SQL, I have problem with this query:

This is my database table ("table_number"):

id      Number      Name
25      1      James
57      3      Doris
78      9      Dave
89      7      Chuck
126      2      Bertie
153      10      Marion
198      5      Veronica
234      6      Allen

If I order it by "Number" it will print:

id      Number      Name

25      1      James
126      2      Bertie
57      3      Doris
198      5      Veronica
234      6      Allen
89      7      Chuck
78      9      Dave
153      10      Marion

Looking at he "Number" series, there are two numbers missing; 4 and 8. I want, in the first place, to find the lowest of the two, i e number 4 in this example.

I have tried to make a query like $SQL = "SELECT * FROM table_numbers WHERE ..." but I don't know how to continue. Please, Gurus of SQL, help me out. If it isn't to much to ask, please comment or explain briefly on the query you suggest. Thanks.

Aaron Tomosky

Does it always start at 1? If there is no 1 is that considered a missing number?

sjklein42

This is an interesting question. Here is an extended discussion along with several proposed solutions, none of which are perfect:

http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/

Do not feel bad. Even the SQL gurus are stumped by this one.

Aaron Tomosky

Therefore I'm trying to define this a little better. For example If what we are really doing is looking for the lowest missing integer starting at 1, that's much easier than looking for a gap in a generic sequence. By adding a rowcount column or a not in (create integer list here) it can be solved much simpler.

Sharath S

try this/

SELECT MIN(rownum) min_Missing_Number 
  FROM (  SELECT id, 
                 Number, 
                 Name, 
                 @rownum := @rownum + 1 AS rownum 
            FROM table_number, 
                 (SELECT @rownum := 0) r 
        ORDER BY Number) t1 
 WHERE Number <> rownum;

Open in new window

sjklein42

Here are a couple of people who think they found good solutions to this query:

http://bytes.com/topic/sql-server/answers/511668-query-find-missing-number

select (a.col1 + 1)
from tab1 a
where not exists
(select 1
from tab1 b
where b.col1 = (a.col1 + 1))
and a.col1 not in
(select max(c.col1)
from tab1 c)
order by 1

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lennart Ericson

ASKER

Superb. Thanks.