• Status: Solved
• Priority: Medium
• Security: Public
• Views: 983

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.
0
Lennart Ericson
• 2
• 2
• 2
• +1
1 Solution

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

Commented:
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.
0

Technology ConsultantCommented:
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.
0

Data EngineerCommented:
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;
``````
0

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

``````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
``````
0

Data EngineerCommented:
``````select * from 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    |
+------+--------+----------+
8 rows in set (0.00 sec)

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;
+--------------------+
| min_Missing_Number |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.00 sec)
``````
0

AmateurAuthor Commented:
Superb. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.