Search code examples
mysqldatabaserow-number

Are there any functions in MySQL like dense_rank() and row_number() like Oracle?

Are there any functions in MySQL like dense_rank() and row_number() like those provided by Oracle and other DBMS?

I want to generate an id within the query, but in MySQL these functions are not there. Is there an alternative?

Solution

  • Mysql doesn't have them, but you can simulate row_number() with the following expression that uses a user defined variable:

    (@row := ifnull(@row, 0) + 1)

    like this:

    select *, (@row := ifnull(@row, 0) + 1) row_number
from mytable
order by id

    but if you're reusing the session, @row will still be set, so you'll need to reset it like this instead:

    set @row := 0;
select *, (@row := @row + 1) row_number
from mytable
order by 1;

    See SQLFiddle.

    dense_rank() is possible but a train wreck; I advise handling that requirement in the app layer.