MySQL 5.7 workarounds for ROW_NUMBER that don't use variables

Question

Using variables, as seen here, to simulate the ROW_NUMBER function found in MySQL8 isn't an option when the query is being used to create a View.

What other kinds of workarounds are available for numbering rows if using variables is not an option?

I'll have to come up with a minimal example. But here's an idea of what I'm trying to do. I need find the time ranges between scheduled lesson events. The following produces the results I want, however, it uses variables to number by rows.

SELECT 
    L1.SCHEDULE_TYPE, L1.STUDIO_ID, L1.ROOM, L1.DAYOFWEEK, L1.END_TIME AS START_GAP, L2.START_TIME AS END_GAP
FROM 
    (SELECT 
        SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, TEACHER_ID, START_TIME, END_TIME
        ,(@row_number1 := @row_number1 + 1) AS ROW_NUMBER
        FROM 
            LESSON, (SELECT @row_number1 := 0) AS x
        ORDER BY 
            SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, START_TIME           
    ) AS L1
JOIN 
    (SELECT 
        SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, TEACHER_ID, START_TIME, END_TIME
        ,(@row_number2 := @row_number2 + 1) AS ROW_NUMBER
        FROM 
            LESSON, (SELECT @row_number2 := 0) AS x
        ORDER BY 
            SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, START_TIME
    ) AS L2 
ON(
    L1.SCHEDULE_TYPE = L2.SCHEDULE_TYPE
    AND L1.TEACHER_ID = L2.TEACHER_ID
    AND L1.DAYOFWEEK = L2.DAYOFWEEK
    AND L1.END_TIME < L2.START_TIME
    AND L1.ROW_NUMBER = (L2.ROW_NUMBER - 1)
)

there is only to add an auto_incrment and the rdrop it, but you wouldn't have partitions, so basically none possibility — nbk, Jul 21 '22 at 19:08
you should show a complete [mre] so that we can understand what you want — nbk, Jul 21 '22 at 19:19
Can you give an example where variables don't work and you need an alternative? Otherwise my only advice is to upgrade to MySQL 8. — Schwern, Jul 21 '22 at 19:33
"*however, it uses variables to number by rows*" is this causing a problem, or are you just curious? — Schwern, Jul 21 '22 at 19:35
@nbk thanks I've added more detail as to what I'm trying to do. I'll have to come up with something more minimal — Kevin, Jul 21 '22 at 19:35
@Schwern it's causing a problem. I want to store the above query as a view. — Kevin, Jul 21 '22 at 19:38
@Kevin Could you show us what you tried to make this a view and what problems you've had? — Schwern, Jul 21 '22 at 19:41
Does this answer your question? [Can I create view with parameter in MySQL?](https://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql) — Schwern, Jul 21 '22 at 19:43

score 1 · Accepted Answer · answered Jul 21 '22 at 20:00

When you need to emulate

SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2)
FROM table

and do not use UDVs then you may use

SELECT *, ( SELECT COUNT(*) 
            FROM table t2 
            WHERE t1.col1 = t2.col1
              AND t1.col2 >= t2.col2 )
FROM table t1

or

SELECT t1.*, COUNT(t2.primary_key)
FROM table t1
JOIN table t2 ON t1.col1 = t2.col1
             AND t1.col2 >= t2.col2
GROUP BY t1.primary_key

MySQL 5.7 workarounds for ROW_NUMBER that don't use variables

1 Answers1