I would like to know if it is possible to create a query that returns the smallest integer available. It's a bit complicated to explain, but with an example it makes it easy:
id | codigo
---|-------
1 | 1
2 | 2
3 | 4
4 | 5
5 | 6
In this case, I would like the query to return the number 3, since it is the smallest integer that is still available. And if a record were inserted containing code 3, return the number 7 because it would become the smallest integer available.
If we consider the following table:
CREATE TABLE 'products' (
'id' int(3) NOT NULL AUTO_INCREMENT,
'name' varchar(20) NOT NULL,
'code' int(3) DEFAULT NULL,
PRIMARY KEY ('id')
)
With the following records:
+----+-----------+------+
| id | name | code |
+----+-----------+------+
| 1 | produto 1 | 1 |
+----+-----------+------+
| 2 | produto 2 | 2 |
+----+-----------+------+
| 3 | produto 3 | 4 |
+----+-----------+------+
| 4 | produto 4 | 6 |
+----+-----------+------+
That is, the lowest available code will be 3. To get it, we do:
SELECT min(products.code + 1) AS code FROM products
LEFT JOIN products AS temp ON products.code + 1 = temp.code
WHERE temp.code IS NULL;
That is, select the lowest value of the code incremented by 1 of a product that does not have a product with a code registered adjacent to it. For example, without using the min function, the returned values would be 3, 5, 7, since the code 2 product does not have an adjacent product at 3, the code product 4 does not have adjacent at 5, and the code product 6 does not have a product in 7. Since the value returned is incremented by 1, it would be 2 + 1, 4 + 1, 6 + 1. Using the min function, we return only the smallest of them: 3.
| code |
|------|
| 3 |
See working in SQLFiddle .
If a record is added with code 3:
+----+-----------+------+
| id | name | code |
+----+-----------+------+
| 1 | produto 1 | 1 |
+----+-----------+------+
| 2 | produto 2 | 2 |
+----+-----------+------+
| 3 | produto 3 | 4 |
+----+-----------+------+
| 4 | produto 4 | 6 |
+----+-----------+------+
| 5 | produto 5 | 3 |
+----+-----------+------+
The result of the same query will be:
| code |
|------|
| 5 |
See working in SQLFiddle .