Get the id which is right before the designated id from a table of non sequential numeric values

Question

This is for MySQL versions prior to 8.0. I apologize in advance as this is not a question which I intend to seek an answer.(But an answer via a different approach is always welcome.) Someone just closed a question which I find too educational to miss. So hereby I post a similar question and then provide an answer for it. The question is straight forward: Get the id which is right before the designated id from a table of non sequential numeric values. First of all,let's create a test table named idtable and insert values of non-sequential order.

create table idtable (id int);
insert idtable values (15),(1),(179),(18),(201),(35);
select * from idtable;

Answer 1

Here comes the elaboration. The select statement above returns a result set of 15,1,179,18,201,35 . As without an index, MySQL has a tendency of using the insert sequence of rows for the select statement when no ORDER BY clause is used. Therefore, we need to add a row sequence for the id values to form a sequenced table. Basically,select id,(@row_id:=@row_id +1) as row_seq from idtable,(select @row_id:=0) t does the trick of building the required table. The rest is to get the sequence of id from the sequenced table, which will be used to get the id that has a sequence value of the said sequence minus 1 . Here is the pseudocode on top of the sequenced table: get id from sequenced table where its row sequence is equal to (row sequence of designated id value - 1) The complete work is demonstrated below using a designated id of 179:

select id 
from (
    select id,(@row_id1:=@row_id1 +1) as row_seq from idtable,(select @row_id1:=0) t
) sequenced_table
where row_seq=
    (
        select row_seq from (
            select id,(@row_id2:=@row_id2+1) as row_seq from idtable,(select @row_id2:=0) t
        ) sequenced_table 
        where id=179
    ) -1;

Please note that even though the same sequenced table is used in the FROM clause and in the WHERE clause of the main query, I used different names for the user variable @row_id. This has something to do with MySQL's inherent nature regarding user variables. Had we used the the same name for the user variable @row_id , the sequence values for the id from the two sequenced tables would have been cumulatively different. You can test it using this statement:

select id,(@row_id:=@row_id +1) as row_seq from idtable,(select @row_id:=0) t
union
select id,(@row_id:=@row_id +1) as row_seq from idtable,(select @row_id:=0) t
;