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

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

enter image description here

mysqlsqlmariadbsequelpro


Solution 1:[1]

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/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ergest Basha

Related Questions