如何选择id仅在空隙之上的行

Question

I have a table containing columns id and name. My focus is on order of id. In fact I want to select the rows, When order of number's id breaks . Look at my example:

// mytable
+----+-----------+
| id |    name   |
+----+-----------+
| 1  |   ali     |
| 2  |   jack    |
| 3  |   peter   |
| 5  |   steve   |
| 6  |   lenord  |
| 7  |   jack    |
| 9  |   fered   |
+----+-----------+

Now I want to select where id=5 and select where id=9. because id=4 and id=8 are removed.

EDIT: I want this output:

// mytable
+----+-----------+
| id |    name   |
+----+-----------+
| 5  |   steve   |
| 9  |   fered   |
+----+-----------+

Is it possible to I do that ?

Answer 1

In fact you want the records with id's just one above the missing id's; so you need to search fo the records with id such that id-1 does not exist (this will always be the case for the lowest id, so we'll have to explicitely exclude id=1 )

SELECT *
FROM the_table tt
WHERE id > 1
AND NOT EXISTS (
   SELECT *
   FROM the_table nx
   WHERE nx.id = tt.id -1
   );