0
I would like to know if there is a possibility of creating a query that returns the smallest integer available. It’s a little complicated to explain, but with one example facilitates:
id | codigo
---|-------
1 | 1
2 | 2
3 | 4
4 | 5
5 | 6
In that case, I would like query return number 3, as it is the smallest integer that is still available. And if a record were inserted containing the code 3, return the number 7, as this would be the smallest integer available.
2
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 smallest code available 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 smallest value of the incremented code in 1 of a product that does not have a registered code adjacent to it. For example, without using the function min, the returned values would be 3, 5, 7, because the code product 2 does not have an adjacent product in 3, the code product 4 does not have an adjacent product in 5 and the code product 6 does not have a product in 7. As the returned value is incremented in 1, it would be 2+1, 4+1, 6+1. Using the function min, We return only the smallest of them: 3.
| code |
|------|
| 3 |
See working on 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 consultation shall be:
| code |
|------|
| 5 |
See working on Sqlfiddle.
Anderson, thank you! You’ve helped a lot, but that way it wouldn’t work if product 1 didn’t exist, right? There would be some way to adapt?
In case, I would like code 1 to return, in case it does not exist.
In fact, I had not tested this possibility. What if I reverse the logic by checking the previous record, using -1 instead of +1? I have no way to test it now, but I think it would work, returning empty if there are no available spaces.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
Regardless of the information... I would just like a query that returns, in this case, the number 3, because it is the next of the order that was skipped. For example, in the database I have 5 records, with the following codes: 1,2,3,6,7. In this case, I would like a query q return the number 4.
– Cristian