# Find which numbers in [1, 161] are not in the result set?

How to find which two number in `[1, 161]` is not in the result set?

Can I find this two numbers using sql command?

``````mysql> select blog_id from wp_blogs;

...

|     149 |
|     150 |
|     151 |
|     152 |
|     153 |
|     154 |
|     155 |
|     156 |
|     157 |
|     158 |
|     159 |
|     160 |
|     161 |
+---------+
159 rows in set (0.25 sec)
``````

The sequence is from 1 to 161, there should be 161 numbers, but only 159 numbers are in the resultset.

``````mysql> select count(blog_id) from wp_blogs;
+----------------+
| count(blog_id) |
+----------------+
|            159 |
+----------------+
1 row in set (0.24 sec)
``````

How to find the missing numbers?

-
I'll add that it's best to come back and mark the answer as accepted once you've taken the time to ask a question and pick our brains.... –  Phil Sep 20 '12 at 1:20

To find gaps in a number range:

Test table and data:

``````mysql> CREATE TABLE wp_blogs
-> (
->   blog_id INTEGER
-> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);
``````

Query:

``````mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
->     FROM wp_blogs AS a, wp_blogs AS b
->     WHERE a.blog_id < b.blog_id
->     GROUP BY a.blog_id
->     HAVING start < MIN(b.blog_id);
+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+
``````
-
i have revised my question, you must misunderstood my question right now ... wrong answer:( –  hugemeow Sep 19 '12 at 19:25
Ok, you want to find the gap in the blog_id numbers? You weren't clear before ;) –  Phil Sep 19 '12 at 19:31
so can you revise your answer? –  hugemeow Sep 19 '12 at 19:37
Answer edited... –  Phil Sep 19 '12 at 19:49