CSiva CSiva - 1 year ago 55
SQL Question

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?

Answer Source

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.