Emulating ROW_NUMBER() in MariaDB sql query returns null when executing query-CodePudding

I'm trying to get the row number of records in my database table, I'm using a variable and am executing the query, I get no errors, but get a null column for num and need to know where I'm going wrong to resolve this:

SET @row_number = 0; 
SELECT 
    token,
    (@row_number:=@row_number   1) AS num
FROM
    tlp_queue_manager_schedulers

CodePudding user response：

You can use a session variable as a derived table and cross join it with the main table to emulate row_number.

Try:

SELECT token,
       (@row_number:=@row_number   1) AS num  
FROM tlp_queue_manager_schedulers,
    (SELECT @row_number:=0) AS t
ORDER BY token ;

Demo

Worth reading: https://www.mysqltutorial.org/mysql-row_number/