Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

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 |
+-------+------+
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.