How to Generate Ranks 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)

How to make a sequence number ranking in mysql

You can fight user variables all day or emulate dense rank function in MySQL like so:

SELECT main.id, main.md_name, main.total_visit, COUNT(DISTINCT prev.total_visit) + 1 AS rank
FROM datamd AS main
LEFT JOIN datamd AS prev ON prev.total_visit > main.total_visit
GROUP BY main.id, main.md_name, main.total_visit
ORDER BY rank

Substitute the "tables" in the above query with the sub queries from your original query:

SELECT datamd.id, datamd.nama_md, main.total_visit, COUNT(DISTINCT prev.total_visit) + 1 AS rank
FROM datamd
LEFT JOIN (
    SELECT idmd, COUNT(*) AS total_visit
    FROM rincian_kunjungan
    WHERE status = '1' AND MONTH(tanggal_kunjungan) = $bulan AND YEAR(tanggal_kunjungan) = $tahun
    GROUP BY idmd
) AS main ON datamd.id = main.idmd
LEFT JOIN (
    SELECT COUNT(*) AS total_visit
    FROM rincian_kunjungan
    WHERE status = '1' AND MONTH(tanggal_kunjungan) = $bulan AND YEAR(tanggal_kunjungan) = $tahun
    GROUP BY idmd
) AS prev ON prev.total_visit > main.total_visit
GROUP BY datamd.id, datamd.nama_md, main.total_visit
ORDER BY rank

Rank users in mysql by their points

This is just a fix of Gordon solution using variables. The thing is your rank function isnt the way rank should work. (student 4 should be rank 4)

SQL Fiddle Demo You can add more student to improve the testing.

select er.*,
       (@rank := if(@points = points, 
                    @rank, 
                    if(@points := points,    
                       @rank + 1, 
                       @rank + 1                       
                      )
                   )                  
       ) as ranking
from students er cross join
     (select @rank := 0, @points := -1) params
order by points desc;

OUTPUT

| id | points | ranking |
|----|--------|---------|
|  1 |     80 |       1 |
|  2 |     78 |       2 |
|  3 |     78 |       2 |
|  4 |     77 |       3 |
|  5 |     66 |       4 |
|  6 |     66 |       4 |
|  7 |     66 |       4 |
|  8 |     15 |       5 |

What is the best way to generate ranks in MYSQL?

This will return the rank as rownum

SELECT @rownum := @rownum + 1 rownum, 
       t.* 
  FROM (SELECT @rownum:=0) r, 
       (SELECT * FROM students ORDER BY gpa DESC) t;

How to create a MySQL 5.6 Rank with JOIN and multiple sorting criteria

I would recommend joining and ordering in a subquery first, then computing the rank. Also, you should not mix implicit and explicit joins - matter of fact, always use explicit joins:

SELECT x.*, @curRank := @curRank + 1 AS rank
FROM (
    SELECT c.id, c.score, i.sheetscore
    FROM chart c 
    LEFT JOIN indicator i ON c.indicator_id = i.id
    ORDER BY c.score DESC, i.sheetscore DESC
) x
CROSS JOIN (SELECT @curRank :=0) q
ORDER BY score DESC, sheetscore DESC

Note that, if you are running MySQL 8.0, this is straight-forward with row_number():

SELECT 
    c.id, 
    c.score, 
    i.sheetscore, 
    ROW_NUMBER() OVER(ORDER BY c.score DESC, i.sheetscore DESC) rn
FROM chart c 
LEFT JOIN indicator i ON c.indicator_id = i.id
ORDER BY c.score DESC, i.sheetscore DESC

How to perform grouped ranking in MySQL

SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, id_student
  ) t

This works in a very plain way:

1. Initial query is ordered by id_class first, id_student second.
2. @student and @class are initialized to -1
3. @class is used to test if the next set is entered. If the previous value of the id_class (which is stored in @class) is not equal to the current value (which is stored in id_class), the @student is zeroed. Otherwise is is incremented.
4. @class is assigned with the new value of id_class, and it will be used in test on step 3 at the next row.

Ranking position without duplicates

Just add the ranking criteria to your WHERE clause:

SELECT *, 
  (
    SELECT 1 + COUNT(*)
    FROM   players p2 
    WHERE  p2.player_game = p.player_game 
    AND
    (
      (p2.player_score > p.player_score) OR
      (p2.player_score = p.player_score AND p2.player_tickets > p.player_tickets) OR
      (p2.player_score = p.player_score AND p2.player_tickets = p.player_tickets AND p2.player_date > p.player_date) OR
      (p2.player_score = p.player_score AND p2.player_tickets = p.player_tickets AND p2.player_date = p.player_date AND p2.player_id > p.player_id)
    )
  ) AS ranking 
FROM players p 
ORDER BY player_game, player_score DESC;

---

Related Topics