Question 22002 in Mysql Question posted in
The official MySQL documentation can be found here.

Select Row Number in MySQL

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

Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search