how to select the rows with id's just one above gaps

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 ?

did you mean `id=4` is removed? id=5 is alive in and well in your sample. and yes, you can use some uglyish queries to "look" around at other rows in the table. — Marc B, Jun 23 '15 at 22:06
If you call an ID in your query that does not exists, you will not get results. So if you query for ids that exists, you will get results. Rows are not like Arrays. They don't re-index unless you tell them to do so. The value of the ID Column will not change when you delete a row. — Twisty, Jun 23 '15 at 22:15
I think I misunderstood your goal. You want to collect the row that is position 4 (ID 5) and position 7 (ID 9) after the other rows are removed. This can be done. — Twisty, Jun 24 '15 at 07:08
@Strawberry What? Do you want something that I want as output ? — , Jun 24 '15 at 10:59
Note: I changed the title. Integrity is *not lost* when there a few gaps in the range of ids. — wildplasser, Jun 24 '15 at 11:52

score 0 · Answer 1 · answered Jun 24 '15 at 07:17

Try seeking versus a specific ID. http://php.net/manual/en/mysqli-result.data-seek.php

<?php
/* Open a connection */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT id, name FROM table";
if ($result = $mysqli->query($query)) {

    /* seek to row no. 4 */
    $result->data_seek(3);

    /* fetch row */
    $row = $result->fetch_row();

    printf ("ID: %s  Name: %s\n", $row[0], $row[1]);

    /* seek to row no. 7 */
    $result->data_seek(6);

    /* fetch row */
    $row = $result->fetch_row();

    printf ("ID: %s  Name: %s\n", $row[0], $row[1]);

    /* free result set*/
    $result->close();
}

/* close connection */
$mysqli->close();
?>

This should return:

ID: 5 Name: steve
ID: 9 Name: fered

Did you seek manually ? In reality my table has +10000 rows. I need to a automatic algorithm. And by the way, `data_seek();` is just for **mysqli**? I'm using **PDO** — , Jun 24 '15 at 10:40
You did not clarify what functions you were using. You did not explain the issue with enough details. http://stackoverflow.com/questions/685871/mysql-data-seek-pdo-equivalent — Twisty, Jun 24 '15 at 16:41

wildplasser · Accepted Answer · 2015-06-24T11:47:35.810

0

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
   );

edited Jun 24 '15 at 11:47

answered Jun 24 '15 at 11:27

wildplasser

43,142
8
66
109

OW MY GOD !!! HOW ?? I thought I know `sql` but ...!!! Thanks ! Just I can't understand it, Can explain me more about your code ?! – Jun 24 '15 at 11:37
Have a new question, Why I can't use `NOT IN` instead of `NOT EXISTS` ? – Jun 24 '15 at 13:06
You probably can, but why should you? – wildplasser Jun 24 '15 at 13:18
I checked it and did not work, there is not mandatory. I just like to know why `NOT IN` does not work ?! – Jun 24 '15 at 13:21

Strawberry · Answer 3 · 2015-06-25T14:10:23.393

0

SELECT x.*
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id = x.id - 1 
 WHERE y.id IS NULL 
   AND x.id > 1;
+----+-------+
| id | name  |
+----+-------+
|  5 | steve | 
|  9 | fered | 
+----+-------+

edited Jun 25 '15 at 14:10

answered Jun 25 '15 at 09:22

Strawberry

33,750
13
40
57

I checked your code and did not see two columns `id` and `name` contained `NULL`. what are that ? You select `x.*`, Then I don't think there be more two columns. – Jun 25 '15 at 12:40

how to select the rows with id's just one above gaps

3 Answers3