User Tools

Site Tools


db:sql_find_seq_max

Finding the next number in a sequence interrupted by gaps

08.01.2010

Ok, let's assume you have a tables with an auto-increment field (id) and you populate it with 100 records. These records were inserted with id's from 1 to 100 in sequence. After that you randomly delete records, creating by that gaps in the id sequence. Next autoincrement will be 101, but what about if you want to reuse these ids (in other words finding the next available id after the first existing sequence).

We assume for testing purpose a table (called person) with 2 fields: id and name (id - autoincrement field, name char). A select all command gives us:

mysql> SELECT * FROM person;
+----+--------+
| id | name   |
+----+--------+
|  2 | John   | 
|  3 | Doe    | 
|  6 | Emily  | 
|  7 | George | 
|  9 | David  | 
+----+--------+
5 rows in set (0.00 sec)

What we need, is to find id = 4 as the next available id for our id sequence. This is how we'll do it (I'll post the code in phases to understand the query):

Using LEFT OUTER JOIN

We select all the records from the 'first' table and then all matching records from the 'second' table with the condition id_first = id_second - 1.
First and second table are in fact the same one.

mysql> SELECT * FROM person AS p1
    -> LEFT OUTER JOIN person AS p2 ON p1.id = p2.id - 1;
+----+--------+------+--------+
| id | name   | id   | name   |
+----+--------+------+--------+
|  2 | John   |    3 | Doe    | 
|  3 | Doe    | NULL | NULL   | 
|  6 | Emily  |    7 | George | 
|  7 | George | NULL | NULL   | 
|  9 | David  | NULL | NULL   | 
+----+--------+------+--------+
5 rows in set (0.00 sec)

The NULL values represent the first element in the gap right after an id.

Selecting only the values before a gap starts

For our case, it will be 3, 7 and 9. There are the values with NULL corespondent in the second table.

mysql> SELECT p1.id FROM person AS p1
    -> LEFT OUTER JOIN person AS p2 ON p1.id = p2.id - 1 
    -> WHERE p2.id IS NULL;
+----+
| id |
+----+
|  3 | 
|  7 | 
|  9 | 
+----+
3 rows in set (0.00 sec)

Last step, select the min value from these

The easy step and the final solution :)

mysql> SELECT MIN(p1.id)+1 AS mingap FROM person AS p1
    -> LEFT OUTER JOIN person AS p2 ON p1.id = p2.id - 1 
    -> WHERE p2.id IS NULL;
+------------+
| mingap     |
+------------+
|          4 | 
+------------+
1 row in set (0.00 sec)

db/sql_find_seq_max.txt · Last modified: 2013/03/16 17:40 (external edit)