MySQL – Simulating LEAD(1) or LEAD(2) Effectively

MySQL

Assume i have a table with the dutch or english alfabet.

Alfabet

digit   
--------
a       
b       
c       
d       
e       
f       
g       
h       
i       
j       
k       
l       
m       
n       
o       
p       
q       
r       
s       
t       
x       
y       
z

Now i want to display a list like this.

digit   next_digit  next_two_digit  
------  ----------  ----------------
a       b           c               
b       c           d               
c       d           e               
d       e           f               
e       f           g               
f       g           h               
g       h           i               
h       i           j               
i       j           k               
j       k           l               
k       l           m               
l       m           n               
m       n           o               
n       o           p               
o       p           q               
p       q           r               
q       r           s               
r       s           t               
s       t           x               
t       x           y               
x       y           z               
y       z           (NULL)          
z       (NULL)      (NULL)

In Oracle (and more databases that support window functions) you can get this list with 

SELECT 
   "digit"
 , LEAD("digit", 1) OVER (ORDER BY "digit" ASC) as next_digit   
 , LEAD("digit", 2) OVER (ORDER BY "digit" ASC) as next_two_digits
FROM 
 alfabet
ORDER BY 
 "digit" asc

demo http://www.sqlfiddle.com/#!4/a11f1/4

Now i want to simulate the window function lead(1) and lead(2) in MySQL.

Now in MySQL i know i can get the same with co-related subqueries like so. 

SELECT 
 *
 , (SELECT 
     *
    FROM 
     alfabet alfabetInner 
    WHERE
      alfabetInner.digit > alfabet.digit     
    ORDER BY 
      digit ASC
    LIMIT 0, 1      
   ) AS next_digit

 , (SELECT 
     *
    FROM 
     alfabet alfabetInner 
    WHERE
      alfabetInner.digit > alfabet.digit     
    ORDER BY 
      digit ASC
    LIMIT 1, 1      
   ) AS next_two_digit   
FROM 
 alfabet  
ORDER BY
 alfabet.digit ASC

Editted because off comment from Evan Carroll

why doesn't the table Alfabet just store the id/row number on it?

Because i wanted to simulate a pure LEAD(1) or LEAD(2) solution without having to need a auto increment sequence without gaps as PRIMARY ID… the window function off Oracle doesnt do annything with a auto increment PRIMARY key also.

demo http://www.sqlfiddle.com/#!9/6a8701/2

Now the question. Do you know even more (effective) LEAD simulation
methodes?

Best Answer

  1. Use MySQL 8, which supports Window Functions
  2. Hack around it..
    1. Generate a sequence of IDS. This whole project makes no sense without them anyway because the result set of a SQL query isn't guaranteed without an ORDER BY.
    2. LEFT-OUTER JOIN the table with itself self join on id+1 = id
  3. MySQL specific, emulate lag with user-defined variables

That looks something like this in MySQL,

SELECT t1.digit, t2.digit
FROM (
    SELECT digit, @rownum1 := @rownum1 + 1 AS id
    FROM
     alfabet  , (SELECT @rownum1 := 0) r
    ORDER BY
     alfabet.digit ASC
) AS t1
LEFT OUTER JOIN ( 
    SELECT digit, @rownum2 := @rownum2 + 1 AS id
    FROM
     alfabet  , (SELECT @rownum2 := 0) r
    ORDER BY
     alfabet.digit ASC
) AS t2
  ON t1.id +1 = t2.id;

You can even make that simpler with 

SELECT digit, previous
FROM (
    SELECT digit, @prev previous, @prev := digit AS prev
    FROM
     alfabet  , (SELECT @prev := '') r
    ORDER BY
     alfabet.digit ASC
) AS t1;
Related Question