Simulate effective LEAD(1) or LEAD(2) in 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

• Use MySQL 8, which supports Window Functions

• Hack around it..

• 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.

• LEFT-OUTER JOIN the table with itself self join on id+1 = id

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

StackExchange Database Administrators Q#187933, answer score: 3