Select Row Number in MySQL-CodePudding

I want to get the rownumber of each row from a MySQL Table. I already read this article and tried the suggested select statement as below,

SELECT @rownum:=@rownum   1 as row_number, 
       t.*
FROM ( 
   select * from myTable
) t,
(SELECT @rownum := 0) r

But I am getting in syntax error as below when I ran in Dbeaver,

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_number, 
       t.*
FROM ( 
   select * from myTable
) t,
(SELECT @r' at line 1

Can someone please help? I am new to MySQL. I am using version 8.0. Once it's tested I would basically want to use this select in my Apache Spark Code

CodePudding user response：

use this one:

SELECT @rownum:=@rownum   1 as row_num, 
       t.*
FROM ( 
   select * from myTable
) t,
(SELECT @rownum := 0) r;

row_number is a reserved keyword of mysql, you can not use it as alias or for any other purpose.

CodePudding user response：

Try something like this.

 SELECT *,   
        ROW_NUMBER() OVER(PARTITION BY 'some column' ) AS row_num  
    FROM my_table

(https://www.javatpoint.com/mysql-row_number-function)