2

Is it possible to query all rows that their ids are sequential after a specific id? (id is an AUTO_INCREMENT column.)

For example, suppose my specific id is 5. Then I want all rows with these ids:

6, 7, 8, 9, ... until this sequence exists. E.g. if there is no row with id=12 then the sequence must be stopped at 11. So desired result would be rows with these ids:

6, 7, 8, 9, 10, 11.

I use MySQL.


EDIT:

An example:

This is my table:

mysql> SELECT * FROM Inheritance ORDER BY id ASC;                     
+-------------+-------------------+------------+-------------+-------+
| classNS     | class             | super      | superNS     | id    |
+-------------+-------------------+------------+-------------+-------+
|             | Data              | Object     |             | 00002 |
|             | Thing             | Object     |             | 00003 |
| BUILD       | DATA              | Data       |             | 00004 |
|             | Dialable          | Data       |             | 00005 |
|             | Phone             | Data       |             | 00006 |
|             | Email             | Data       |             | 00007 |
|             | Place             | Data       |             | 00008 |
|             | Website           | Data       |             | 00009 |
|             | SocialNetworkLink | Data       |             | 00011 |
|             | Password          | Data       |             | 00012 |
|             | User              | Thing      |             | 00013 |
|             | Asset             | Thing      |             | 00014 |
|             | Collection        | Asset      |             | 00015 |
|             | Product           | Asset      |             | 00016 |
| collections | School            | Collection |             | 00017 |
| collections | LangSchool        | Collection |             | 00018 |
| BUILD       | OWNER             | DATA       | BUILD       | 00022 |
| BUILD       | OWNER_OF_PRODUCT  | DATA       | BUILD       | 00023 |
| BUILD       | OWNER_OF_EMAIL    | DATA       | BUILD       | 00024 |
| BUILD       | COUNTRY           | DATA       | BUILD       | 00025 |
| BUILD       | SOCIAL_NETWORK    | DATA       | BUILD       | 00026 |
| BUILD       | PROVINCE_IR       | DATA       | BUILD       | 00027 |
| collections | SchoolPlus        | School     | collections | 00028 |
+-------------+-------------------+------------+-------------+-------+
23 rows in set (0.00 sec) 

Then I want this result (when my specific id is 5):

+---------+----------+-------+---------+-------+
| classNS | class    | super | superNS | id    |
+---------+----------+-------+---------+-------+
|         | Phone    | Data  |         | 00006 |
|         | Email    | Data  |         | 00007 |
|         | Place    | Data  |         | 00008 |
|         | Website  | Data  |         | 00009 |
+---------+----------+-------+---------+-------+

Notice that the row with id=10 is absent in the source table.

2

A query that doesn't use recursive CTEs or window functions or variables:

SELECT i.* 
FROM Inheritance AS i
    JOIN
    ( SELECT ui.id 
      FROM Inheritance AS ui
      WHERE ui.id > 5
        AND NOT EXISTS
            ( SELECT 1
              FROM Inheritance AS pi
              WHERE pi.id = ui.id - 1
            ) 
      ORDER BY ui.id
      LIMIT 1
    ) AS up
    ON  i.id < up.id
WHERE i.id > 5 ;
  • 1
    If you are in version 8.0 and you cna use the window function solution, it will probably be more efficient than this. But test! There is nothing better than testing performance of all the suggested solutions -in your setup and with your data (size, distribution matter). – ypercubeᵀᴹ Oct 9 '18 at 12:51
  • I could understand the logic. Isn't better to write WHERE pi.id = ui.id - 1 LIMIT 1 instead of just: WHERE pi.id = ui.id - 1? Seems this way we can prevent some extra explorations. – Mir-Ismaili Oct 9 '18 at 17:09
  • 1
    No, inside a EXISTS or NOT EXISTS subquery, LIMIT 1 does nothing. As soon as 1 matching row is found, it doesn't search for more anyway. – ypercubeᵀᴹ Oct 9 '18 at 17:11
  • Thanks. I learned a lot of things, instead of just my answer. – Mir-Ismaili Oct 9 '18 at 17:13
1

As a recursive CTE in MySQL-8.0+ or MariaDB-10.2+

with recursive prior as (
select * from num where i = 5
union
select n.*
from num n
join prior on prior.i+1=n.i
)
select * from prior;

fiddle

1

I am pretty sure the Tabibitosan Method can be adjusted for any RDBMS that supports analytic functions.

Article for the Oracle version: https://community.oracle.com/docs/DOC-915680

The concept uses analytic functions to identify groups of incremental gap free values.

value - row_number() over (order by ____) is constant for a group of sequential gap free numbers. Each value (of this calculation) represents a unique group of consecutive values.

1

It is possible with straight-up SQL - in mySQL version 8 onwards, using the row_number() function.

Also - it doesn't matter if your target ID does not exist in the table (ie. the value '5' in your question - this solution will work whether or not you have '5' in your table).

If you want to see how the row_number() function is used to determine whether a row should be kept in the result set or not, uncomment the two columns I have added that help illustrate the logic, then run the inner SELECT statement on its own.

select workingTable.id from
(
  select 
    id,
--    id - 5 as idCheck, -- update the '5' here. this column is unnecessary for the solution
--    row_number() over w as rowNum, -- this column is unnecessary for the solution
    (id - 5) - (row_number() over w) as checkVal -- update the 5 here
from myTable
where id > 5 -- and update the 5 here
window w as (order by id)
  ) as workingTable
where workingTable.checkVal = 0
;

Here is the DDL to create and populate the table:

create table myTable (id int);

insert into myTable (id) values (4);
insert into myTable (id) values (5);
insert into myTable (id) values (6);
insert into myTable (id) values (7);
insert into myTable (id) values (8);
insert into myTable (id) values (9);
insert into myTable (id) values (10);
insert into myTable (id) values (11);
insert into myTable (id) values (13);
insert into myTable (id) values (14);

And here is the db-fiddle to test.

Here is the same solution, without comments and unnecessary columns, in more compact format:

select workingTable.id from
(   select 
    (id - 5) - (row_number() over w) as checkVal -- update
    from myTable
    where id > 5 -- update
    window w as (order by id)
    ) as workingTable
where workingTable.checkVal = 0
;
0
SELECT t1.*
FROM table_name t1, table_name t2, (SELECT @id := 5) dummy
WHERE t1.id > @id
  AND t1.id = t2.id + 1;

If it is possible that the "specific id" may be absent in the table, alter the last condition as

  AND t1.id = COALESCE(t2.id, @id) + 1

PS. The solution which uses variables seems to be more safe.

0

I found a way: This works. But I don't know is reliable or not (see here):

SET @id = 5;

SELECT *
FROM table_name
FORCE KEY (id)
WHERE id = @id + 1 AND @id := @id + 1
ORDER BY id ASC;

Or better is to initiate @id with 5+1 and then use @id instead of @id+1 in comparison (WHERE id=@id AND ...).


You can't write WHERE clause like below (I don't know why):

WHERE id = (@id := @id+1)

UPDATE:

See unexpected result (!) (according to the sample table in the question):

mysql> SET @id = 5;                                                              
Query OK, 0 rows affected (0.00 sec)                                             

mysql> SELECT * FROM Inheritance FORCE KEY (id) WHERE id = (@id := @id+1) ORDER BY id ASC;                                                                        
+-------------+------------------+--------+-------------+-------+                
| classNS     | class            | super  | superNS     | id    |                
+-------------+------------------+--------+-------------+-------+                
| BUILD       | OWNER            | DATA   | BUILD       | 00022 |                
| BUILD       | OWNER_OF_PRODUCT | DATA   | BUILD       | 00023 |                
| BUILD       | OWNER_OF_EMAIL   | DATA   | BUILD       | 00024 |                
| BUILD       | COUNTRY          | DATA   | BUILD       | 00025 |                
| BUILD       | SOCIAL_NETWORK   | DATA   | BUILD       | 00026 |                
| BUILD       | PROVINCE_IR      | DATA   | BUILD       | 00027 |                
| collections | SchoolPlus       | School | collections | 00028 |                
+-------------+------------------+--------+-------------+-------+                
7 rows in set (0.00 sec)                                                         

mysql> SELECT @id;                                                               
+------+                                                                         
| @id  |                                                                         
+------+                                                                         
|   28 |                                                                         
+------+                                                                         
1 row in set (0.00 sec)  

Or (when @id initiate with 2):

mysql> SET @id = 2;                                                              
Query OK, 0 rows affected (0.00 sec)                                             

mysql> SELECT * FROM Inheritance FORCE KEY (id) WHERE id = (@id := @id+1) ORDER BY id ASC;                                                                        
+-------------+-------------------+------------+---------+-------+               
| classNS     | class             | super      | superNS | id    |               
+-------------+-------------------+------------+---------+-------+               
|             | SocialNetworkLink | Data       |         | 00011 |               
|             | Password          | Data       |         | 00012 |               
|             | User              | Thing      |         | 00013 |               
|             | Asset             | Thing      |         | 00014 |               
|             | Collection        | Asset      |         | 00015 |               
|             | Product           | Asset      |         | 00016 |               
| collections | School            | Collection |         | 00017 |               
| collections | LangSchool        | Collection |         | 00018 |               
+-------------+-------------------+------------+---------+-------+               
8 rows in set (0.00 sec)                                                         

mysql> SELECT @id;                                                               
+------+                                                                         
| @id  |                                                                         
+------+                                                                         
|   25 |                                                                         
+------+                                                                         
1 row in set (0.00 sec) 
  • I don't know is reliable or not The variable was initialized, so it is reliable if you guarantee that the query is executed in the same connection. If not, you must init the variable in fake table in FROM section, like FROM table_name, (SELECT @id := 5) dummy. why I can't write WHERE clause like below If so you will compare id with the @id after re-assign, whereas in present state you compare id with the @id before re-assign. – Akina Oct 9 '18 at 4:42
  • PS. Try to add id > 5 condition (not > @id !) to the WHERE section. It can increase the performance, especially when @id is far from start. – Akina Oct 9 '18 at 4:48
  • @Akina; Thanks for your explanation about reliability and performance tip. About another note: Yes, I will compare id with @id after re-assign. I think the process should be: @id will be 6 and then the comparison is done. Then @id will be 7 and then the comparison is done. And so on .... Where is the problem? – Mir-Ismaili Oct 9 '18 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.