Solved

# Find the missing number

Posted on 2011-03-16
810 Views
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
Question by:lericson
• 2
• 2
• 2
• +1

LVL 38

Expert Comment

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

LVL 16

Expert Comment

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

LVL 38

Expert Comment

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

LVL 40

Expert Comment

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

LVL 16

Expert Comment

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

LVL 40

Accepted Solution

Sharath earned 500 total points
``````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

Author Closing Comment

Superb. Thanks.
0

## Featured Post

### Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …