CODEWITHSUNDEEP
mysql
Shomit
Shomit

Reputation: 63

Ranking the results in mysql (mysql equivalents for 'dense_rank()' or 'row_number()' functions in oracle)

Below is the sample data:

dept_id  salary
10       10000
10       20000
10       20000
10       30000
20       50000
20       60000

I want to group the department id and then generate the rank values on salary in ascending order. The output should look as

dept_id salary Rank

10  10000  1
10  20000  2
10  20000  2
10  30000  3
20  50000  1
20  60000  2

I'm using the following code:

set @pk1 ='';
set @rn1 =1;
set @sal ='';
set @val =1;

SELECT  dept_id,
        salary,
        denseRank
FROM
(
  SELECT  dept_id,
          salary,
          @rn1 := if(@pk1=dept_id, if(@sal=salary, @rn1, @rn1+@val),1) as denseRank,
          @val := if(@pk1=dept_id, if(@sal=salary, @val+1, 1),1) as value,
          @pk1 := dept_id,
          @sal := salary     
  FROM
  (
    SELECT  dept_id,
            salary
    FROM    emp
    ORDER BY dept_id,salary
) A
) B;

This is working fine for a small subset of data, but gets really slow and takes forever while running this for the whole data (thousands of rows). Is there a better way or some predefined function (similar to dense_rank() or row_number() in oracle) to get this task done more efficiently.

Upvotes: 2

Views: 1348

Answers (1)

Strawberry
Strawberry

Reputation: 33935

Dunno if it's faster but seems like a lot less typing...

SELECT x.*
     , IF(@prev = dept_id
                , IF(salary <> @psalary,@i:=@i+1,@i:=@i)
         ,@i:=1) rank
     , @psalary := salary
     , @prev := dept_id prev 
  FROM my_table x
     , (SELECT @i:=1,@prev:='',@psalary:='') vars 
 ORDER 
    BY dept_id,salary;

Upvotes: 1

Related Questions