Menu
I have this table in MySQL, for example:
ID | Name 1 | Bob 4 | Adam 6 | Someguy If you notice, there is no ID number (2, 3 and 5).
How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?
678
SELECT t1. id+1 AS 'Missing From', MIN(t2.id) - 1 AS 'To' FROM table AS t1, table AS t2 WHERE t1.id < t2.id GROUP BY t1.id HAVING t1.id < MIN(t2.id) - 1; This query gives the following result. Using this dataset we can figure out where the gaps in the data are and perhaps do something with them.
Authentication ID Each row in the mysql. user table is identified by a user and host tuple. This tuple is the authorization ID. A client can authenticate with an authorization ID and a password. The ID is then referred to as a user or user name.
SELECT a.id+1 AS start, MIN(b.id) - 1 AS end FROM testtable AS a, testtable AS b WHERE a.id < b.id GROUP BY a.id HAVING start < MIN(b.id) Hope this link also helps http://www.codediesel.com/mysql/sequence-gaps-in-mysql/
178
A more efficent query:
SELECT (t1.id + 1) as gap_starts_at, (SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at FROM my_table t1 WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1) HAVING gap_ends_at IS NOT NULL If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
