-1

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

Harikrishnan Balachandran
  • 570
  • 9
  • 24

2 Answers2

1

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.

Divya Prakash
  • 898
  • 1
  • 6
  • 14
  • Thanks much! It worked. Not sure why the other post has this as accepted solution – Harikrishnan Balachandran Nov 29 '22 at 16:03
  • @HarikrishnanBalachandran since your version of MySql is 8.0 you should not use variables to get row numbers. Take some time to read about ROW_NUMBER() window function and use it. – forpas Nov 29 '22 at 16:05
  • Thank you for the input. Let me check it. I was in a hurry. Will spend some time – Harikrishnan Balachandran Nov 29 '22 at 16:08
  • this will work even in mysql 8 (the error you got is just because "row_number" is now a reserved word), but as of mysql 8 setting variables in a select is officially deprecated; you can expect it to stop working in some future version. – ysth Nov 29 '22 at 16:22
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)

drdalle
  • 9
  • 2
  • 1
    we still don't know which mysql version he has – nbk Nov 29 '22 at 15:48
  • He says he is using version 8.0 – drdalle Nov 29 '22 at 15:51
  • I want to select the entire row along with row number. I don't want it to be partitioned on some column. Or then in my case it has be over partitioned on all the columns in the tables. There is a rownum, * in oracle that gets the rownum along with each record in table. Isn't there anything similar or easier to use? – Harikrishnan Balachandran Nov 29 '22 at 15:56
  • You don't need the partition by, but you should specify an order by in the over clause – ysth Nov 29 '22 at 15:58
  • Can you please share an example query? I don't have any PK in my table. I have 100000 rows in my table. What i need is rownum column from 1,2,.. 100000 along with all the other columns. – Harikrishnan Balachandran Nov 29 '22 at 16:02
  • then just `SELECT *, ROW_NUMBER() OVER () as row_num`. but be aware that sql data does *not* have any innate order; there's no guarantee the row number you get will have any reproducable order beyond the one query. – ysth Nov 29 '22 at 16:21
  • @ysth - I tried this. SELECT *, ROW_NUMBER() OVER(ORDER by column ) AS row_num FROM myTable. myTable has 120M records. The query seems to run forever. Is there a better way? – Harikrishnan Balachandran Nov 29 '22 at 16:36
  • how long does it take with just `select *`? adding `ROW_NUMBER() OVER ()` shouldn't make it take longer (with no order by). but then the row number is just something you could set in your client anyway. maybe take a step back and edit your question to say *why* you want a row number, what you intend to use it for? maybe even consider adding a PK to your table (mysql will be creating a hidden one for you anyway, at least with innodb) – ysth Nov 29 '22 at 17:13