Rank Function in MySQL

Rank function in MySQL

One option is to use a ranking variable, such as the following:

SELECT    first_name,
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

The (SELECT @curRank := 0) part allows the variable initialization without requiring a separate SET command.

Test case:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Result:

+------------+------+--------+------+
| first_name | age  | gender | rank |
+------------+------+--------+------+
| Kathy      |   18 | F      |    1 |
| Jane       |   20 | F      |    2 |
| Nick       |   22 | M      |    3 |
| Bob        |   25 | M      |    4 |
| Anne       |   25 | F      |    5 |
| Jack       |   30 | M      |    6 |
| Bill       |   32 | M      |    7 |
| Steve      |   36 | M      |    8 |
+------------+------+--------+------+
8 rows in set (0.02 sec)

Alternative of rank function in mysql 5.7 to find out latest rescord grouped by cr-id

After adding a rank column, do:

set @last='';
set @rank=NULL;
update yourtable
    set rank=if(
        @last=(@last:=cr_id),
        (@rank:=@rank+1),
        (@rank:=1)
    )
order by cr_id,`timestamp`;

how to rank using count function in mysql

I solved it using below query:

select *, (
    select Count(distinct salary) -1
    from employees e2 
    where e2.deptId= e.deptId 
      and (e2.salary > e.salary or (e2.salary = e.salary and e2.id <=e.id))
) rownum
from employees e;

This query works as a dense_rank()

Rank() over Partition by in mysql

Try this query: - MySql does not support Rank() function.

select result.id,result.login,result.rank from (
  SELECT    id,
              login,
              IF(login=@last,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
              @_sequence:=@_sequence+1,
              @last:=login
    FROM      ds , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r
    ORDER BY  id asc) as result;

Hope it helps you!

---

Related Topics