Are there any functions in MySQL like dense_rank() and row_number() like Oracle?
Solution 1
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.
Solution 2
We have now..
select ename, sal, dense_rank() over (order by sal desc)rnk
from emp2 e
order by rnk;
Solution 3
MySQL doesn't support these functions, but you can mimic them yourself. Shamelessly link to my solution to ROW_NUMBER, RANK and DENSE_RANK functions in MySQL
Solution 4
In MySql
you dont have dense_rank()
or row_number()
like the one in Oracle
.
But you can create the same functionality through SQL
query:
Here is an article doing the same:
CSiva
Updated on July 13, 2022Comments
-
CSiva almost 2 years
Are there any functions in MySQL like
dense_rank()
androw_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?
-
CSiva over 8 yearsBut the numbers are keep on increasing how many times i execute the query.
-
Bohemian over 8 years@CSiva see edit for how to overcome the "ever-increasing row number" problem.