Re: remove gaps in a table
From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 20 Dec 2017 23:31:33 GMT
Message-ID: <FIRSTGAP-20171221003101_at_ram.dialup.fu-berlin.de>
Date: 20 Dec 2017 23:31:33 GMT
Message-ID: <FIRSTGAP-20171221003101_at_ram.dialup.fu-berlin.de>
mireero <mireero_at_free.fr> writes:
>I am still wondering how one could find the first gap, using mysql only ?
mysql> CREATE TABLE T( C INT );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO T( C )VALUES( 0 ),( 1 ),( 2 ),( 3 ),( 5 ),( 6 ),( 7 ),( 9 );
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
[Quoted] mysql> SELECT C + 1 AS FIRSTGAP FROM T WHERE NOT EXISTS( SELECT D FROM( SELECT C AS D FROM T )AS T1 WHERE C + 1 = D )ORDER BY 1 LIMIT 1;
+----------+
| FIRSTGAP |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
Received on Thu Dec 21 2017 - 00:31:33 CET