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
``````

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.

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

• why doesn't the table Alfabet just store the id/row number on it? Oct 7, 2017 at 20:50
• @Evan Carroll 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. Oct 8, 2017 at 10:55

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