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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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.