4

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?

2
  • 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 Answer 1

3
  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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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