17

Is there a way to get the row number in Mysql like the rownum in oracle, If not then is there any indirect way of doing it? please suggest.

Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
  • There is no rank functionality. Refer this [Stackoverflow question][1] [1]: http://stackoverflow.com/questions/1895110/row-number-in-mysql – mehul9595 Dec 14 '11 at 18:58

2 Answers2

32

Until MySQL finally supports modern SQL, the only way to get something similar is this: 

SELECT @rownum:=@rownum + 1 as row_number, 
       t.*
FROM ( 
   < your original query goes here >
) t,
(SELECT @rownum := 0) r
-6

The best practice is to add an auto incrementing ID for each row. This would also help you a lot if, one day, you choose to use relational tables.

In phpMyAdmin, when you create (or edit) a table, you'll see the AUTO_INCREMENT option. You can only have one column with auto IDs enabled.

Edit: This is how you database would look like:

id | name  | email | phone
1    Tekin   ...     ...
2    John    ...     ...
3    Jane    ...     ...
  • 5
    That is something completely different to a `row_number()` value. What if you want to get the row number after sorting by a specific column? –  Dec 14 '11 at 18:53