Categories MySQL

Query to obtain rank function in MySQL

Updated: Comments Link55 Comments

There is no rank function in MySQL actually, whenever you need to query rank from table based on some functionality/criteria in MYSQL. But there are simple queries that can be used to perform the same.

Here I’m describing queries to perform rank with examples:

First of all we are creating players table where we want to get the rank. You will be familiar with many queries depending on various requirements.

Query to obtain rank function in MySQL

Here we wish to obtain a rank column with ascending order of player’s age. So our query will be:

To declare a variable in mysql you have to use ‘@’ sign before variable name.  The (@curRank := 0) part in FROM clause allows us the variable initialization without requiring a separate SET command. You can use SET as well but it will process two queries:

Query to calculate rank in descending order

More ever to obtain rank in descending order of age then name, modify the query at ORDER BY clause with DESC and column name:

Rank in MySQL when rows ties

Now what if  we wish to assign same rank to rows with ties means rows with same values for the rank comparison column (age in our case) should hold the same rank while calculating rank in MySQL. For this purpose we have used an extra variable.

As shown when two or more rows for a rank in the same partition, each tied rows receives the same rank. Players Andre, Vino, John and Tom have the same age so they are are ranked number 2. The player with next highest age (Brian) is ranked number 3.  This query is equivalent to DENSE_RANK() function in MSSQL and ORACLE.

RANK() equivalent in MySQL

while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties.

Here is a subquery in query. We are using three variables to calculate the rank in case of ties and to omit additional columns in query result (@incRank, @prevRank, @curRank) we have enclosed subquery into query. It’s the RANK() function available in MSSQL and ORACLE.

Here we can see that players Andre, Vino, John and Tom have the same age so they are are ranked number 2. The player with next highest age (Brian) is ranked number 6 instead of 3, because there are four rows that are ranked at position 2.

Well I hope after seeing these examples, you will understanding the difference between the RANK() and DENSE_RANK() as well as will know where to use which query to obtain rank function in MySQL.

You Might Interested In

55 COMMENTS

LEAVE A REPLY
  1. Shawna says:

    Is it possible to show an example of dense rank by category? I’m basically trying to categorize by patient ID and dense rank dates (same dates have the same ranking). I’m stuck with MySQL 5.7. I was able to use your tutorial for dense rank, but can’t figure out by category.

    Ex:

    Patient ID, Date, Dense Rank
    1,2023-01-12,1
    1,2023-01-12,1
    2,2023-01-30,1
    2,2023-02-01,2
    2,2023-02-01,2
    3,2023-02-03,1
    3,2023-02-03,1

    The goal is to filter on dense rank value 1

    Reply
  2. Razi says:

    SELECT id, name, score, rank FROM
    (SELECT id, name,score,
    @curRank := IF(@prevRank = score, @curRank, @incRank) AS rank,
    @incRank := @incRank + 1,
    @prevRank := score
    FROM quiz_users p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r
    ORDER BY score DESC) s;

    how to group by this query by mobile number and sort ranking by addition of score

    Reply
  3. James C says:

    Thank you Amit for your code, but it does not work for me. Below is what I modified yours to: but it returned nothing on previewing in browser, despite that there are sample data added to the db already.

    The error is “No score added yet into JS 1 SARDIUS for 1st Term 2021/2022 Academic Session!”

    $result_class=$conn->query(“SELECT regno, totalscore, subject, ca1, ca2, exam, grade, remarks, name, rank FROM
    (SELECT regno, totalscore, subject, ca1, ca2, exam, grade, remarks, name,
    @curRank := IF(@prevRank = totalscore, @curRank, @incRank) AS rank,
    @incRank := @incRank + 1,
    @prevRank := totalscore
    FROM student_marks p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r WHERE (class=’$class’ OR class=’$class2′) AND term=’$term’ AND year=’$year’ AND cmt!=0 ORDER BY totalscore DESC s “);

    Reply
  4. mabdelaziz77 says:

    Thank you!
    This tutorial helped me too much to implement the rank() function in an old mariadb version.

    Reply
  5. Patrick D says:

    Thank you Amit Sonkhiya for sharing your knowledge, it’s a pleasure for me to learn something new.
    You helped me to assign increasing rank to rows with ties :

    id | rne | rank
    --------------------------------
    13651 | 0861145C | 1
    427 | 0861219H | 1
    10733 | 0861223M | 1
    10325 | 0861228T | 1
    1256 | 0861231W | 1
    1424 | 0861231W | 2
    884 | 0861235A | 1
    6584 | 0861252U | 1
    540 | 0861253V | 1
    568 | 0861253V | 2
    1324 | 0861253V | 3
    1337 | 0861253V | 4
    1451 | 0861253V | 5
    1512 | 0861253V | 6
    13161 | 0861291L | 1
    633 | 0861318R | 1

    with a few changes

    SELECT id, rne, rank FROM
    (SELECT id, rne,
    @curRank := IF(@prevRank = rne, @curRank+1, @incRank) AS rank,
    @incRank := 1,
    @prevRank := rne
    FROM liste_rne p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r
    ORDER BY rne,id) s

    Reply
  6. Adejumo Timilehin says:

    SELECT pid, name, age, rank FROM
    (SELECT pid, name, age,
    @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
    @incRank := @incRank + 1,
    @prevRank := age
    FROM players p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r
    ORDER BY age) s

    running this exact code gives me [BLOB – 1 B] in my rank column, please treat as urgent

    Reply
  7. R Williams says:

    i get errors everywhere from the second (select etc..

    1)An expression was expected. (near “(” at position 73)
    2) Unexpected token. (near “(” at position 73)

    but the second e.g. where it does 2 calculations worked fine.

    Reply
    1. Amit Sonkhiya says:

      SELECT pid, name, age, rank FROM
      (SELECT pid, name, age,
      @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
      @incRank := @incRank + 1,
      @prevRank := age
      FROM players p, (
      SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
      ) r
      ORDER BY age) s WHERE pid = YOUR_PLAYER_ID

      Reply
      2. israel henry says:

        sir i am having this dificulty in ranking the student…i am suppose to rank the student in a particular session or year
        but this script is ranking the student from all the class and session or years at the same time

        SELECT student_ID, total_s, rank FROM
        (SELECT student_ID, total_s,session,term,class,
        @curRank := IF(@prevRank = total_s, @curRank, @incRank) AS rank,
        @incRank := @incRank + 1,
        @prevRank := total_s
        FROM tb_grade p, (
        SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
        ) r ORDER BY total_s DESC) s WHERE session=’2016/2017′ and term=’third term’ and class=’jss1 a’

        https://uploads.disquscdn.com/images/4012dc481e8e37c1bd6090190875087561464b8c66169e5d4a28bfbe6955b291.png https://uploads.disquscdn.com/images/871b77b5c625dce21b21c36bc31892b3d71d07443caf35c3a319e39a81b913f9.png

        Reply
          2. Amit Sonkhiya says:

            SELECT student_ID, total_s, rank FROM
            (SELECT student_ID, total_s,session,term,class,
            @curRank := IF(@prevRank = total_s, @curRank, @incRank) AS rank,
            @incRank := @incRank + 1,
            @prevRank := total_s
            FROM tb_grade p, (
            SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
            ) r WHERE session=’2016/2017′ and term=’third term’ and class=’jss1 a’ ORDER BY total_s DESC) s

  9. Andres Perez says:

    This is really helpful. However, when I do it I do get a rank, but it’s not correct. @jaymitghadiya:disqus Would it be possible to email you and show you what I have? I’ve tried for an hour and still no luck!

    Reply
  10. jaymit ghadiya says:

    hello, Thank for above queries, its really helpful,
    In my case, if we have rank depends on two column, lets say score and time,
    and i want rank on highest score and lowest time, and if user have same score and same time then rank should be same, for example rank can be 1,1,2,3,3,4….

    Reply
  11. Nana Akomeah says:

    Although this post is about 2 years plus old, this is the best of all I have come across on the web, even as new as I am to mysql, I can grasp something. Infact you are the best tutor.
    However, can you please help me with a clear example as to how to query students transcript table, rank them on individual subjects, add overall rank and finally join on another table to select the student’s residence?
    Please, I need this help, and would be very glad for your help.
    Thanks in advance!

    Reply
    1. Amit Sonkhiya says:

      Hello Nana,

      Thanks for the appreciation. I hope you have already got an idea to rank student on individual subject (Your first question). For the rest I would suggest you to run 1-2 more queries separately and make intermediate calculation (overall rank) to get final result in your script (like php).

      Still if you need more help then mail us your table SQL (structure + data) and desired result which you wish to obtain at support@fellowtuts.com

      Thanks

      Reply
      1. Nana Akomeah says:

        Hi Amit,
        Thanks once again for your quick response.
        Actually what I want to achieve is to have an order of merit for students.
        And more so, please what do you mean by ‘intermediate calculation’, I mean can you be a bit specific, as i’m new.
        On the other hand, what about performance, if I choose to go by the separate queries as suggested?

        Reply
        1. Amit Sonkhiya says:

          Hi Nana,

          Intermediate calculation means like making sum to get overall rank or any calculation to pass in another query.

          Performance: A few extra microseconds won’t affect your site performance if you haven’t too much traffic at present. Passing next query or making query inside query has almost same performance impact in your case.

          Reply
      2. Nana Akomeah says:

        Please, something like this is what I’m trying to achieve, but unfortunately, is not working:
        “SELECT t.*,
        COUNT(DISTINCT t.subjects) AS subjectenrollment,
        u.urefnumber,
        u.uresidence
        FROM (
        SELECT r.*,
        @curRank := IF( @prevRank = finalscore, @curRank, @incRank ) AS position,
        @incRank := @incRank + 1,
        @prevRank = finalscore
        FROM studentsreports r, (SELECT @curRank := 0, @prevRank = NULL, @incRank := 1) c
        ORDER BY finalscore ASC
        ) t
        LEFT JOIN studentstbl u ON t.studref = u.urefnumber
        WHERE t.author = :staff
        AND t.studentname = :student
        AND t.academicyr = :year
        AND t.academicterm = :term
        AND t.program = :program
        AND t.classes = :level
        AND t.subjects = :subject”;
        Please, help me deal with this challeng!
        Thanks once again!

        Reply
  12. Kranthi Kumar says:

    Hi ,

    I have one query please help me to resolve it. We have to
    reset the rank value for every set of Ticket number. We have first 4 columns(Ticket
    No, Date, losscode1, losscode2) in my database I need to
    generate next to 2 columns(Rank_losscode1, Rank_losscode2).

    Thanks
    in Advance.

    Reply
          1. Amit Sonkhiya says:

            Hello @Genesis

            Please email us your exact requirement along with queries to create table and sample data SQL scripts

          4. Amit Sonkhiya says:

            Hello @genesis_bertiz:disqus
            For Event, add a WHERE condition after ORDER BY clause, something like order by no_of_events where food_menu = ‘soup 2’

            Same is as a separate query for cost, if you want to get all in one query, you would need to iterate among each set of matching row using GROUP BY clause and calculate rank for both columns with separate subqueries

  14. anil kumar says:

    Hi, Your tutorial was useful, how to get the o/p in

    pid name age rank

    10 A 19 1

    11 B 19 2

    12 C 19 3

    13 D 20 1

    14 E 20 2

    15 F 20 3

    16 G 20 4

    17 H 28 1

    Reply
  15. Fujimaru R says:

    thanks for thetutorials…
    i have a little problem,, how to show result on html? i already try to call query and write to my html table but nothing happen.
    please help.
    thank you

    Reply
    1. Amit Sonkhiya says:

      Hi,

      First check if you are getting result from DB by:
      Running select query in phpMyAdmin directly if it produces result.
      If you are using php then you can check using var_dump($your_var) where $your_var is variable that is holding result from DB.

      Still if you persist issue then you can send us your query and table structure & data from DB at support@fellowtuts.com

      Reply
  16. Ali says:

    How would you “partition by” in mySQL? For example, lets say your data set had another column for City, and you wanted to rank people within their City, so that each City has a 1 till X?

    Reply
    1. Amit Sonkhiya says:

      Ali,

      I didn’t test exactly as you required still I hope the code below would help you. Don’t forget to add city column in our example above.

      SELECT pid, name, city, age, rank FROM
      (SELECT pid, name, city, age,
      @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
      @incRank := @incRank + 1,
      @prevRank := age
      FROM players p, (
      SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
      ) r
      ORDER BY city) s

      Reply
  17. supportgej says:

    help Sonkhiya, please help me out, i have been reading a couple of post but cant find any help, i want your query above to get the rank of a single user, i am new in MYSQL please, can you post a query to help me do that following your above tutorial.

    thank you

    Reply
    1. Amit Sonkhiya says:

      SELECT pid, name, age, rank FROM
      (SELECT pid, name, age,
      @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
      @incRank := @incRank + 1,
      @prevRank := age
      FROM players p, (
      SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
      ) r
      ORDER BY age) s WHERE s.pid = 2

      //RANK() equivalent in MySQL. The last 2 is id of particular user

      Reply
      1. supportgej says:

        Thank you so much for your reply it worked perfectly, but i want the WHERE parameters to be more than one, so i added a new column to the table called TEAM, and i want the query to fetch the ranking in this manner.

        SELECT pid, name, age, team, rank FROM
        (SELECT pid, name, age, team,
        @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
        @incRank := @incRank + 1,
        @prevRank := age
        FROM players p, (
        SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
        ) r
        ORDER BY age DESC) s WHERE team=’aa’

        It Gave Me the Result Below

        Samual | 25 | 2
        Tom | 20 | 8
        Andre | 20 | 8
        Peter | 19 | 12

        But i want the results to be returned in this manner

        Samual | 25 | 1
        Tom | 20 | 2
        Andre | 20 | 2
        Peter | 19 | 4

        so therefor, making the query to give me the rank within a particular group.

        thank you so much, your truly making a positive impact on me.

        Reply
        1. Amit Sonkhiya says:

          SELECT pid, name, age, team,rank from (
          SELECT pid, name, age, team,
          @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
          @incRank := @incRank + 1,
          @prevRank := age FROM
          (SELECT pid, name, age, team
          FROM players p, (
          SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
          ) r
          ) s WHERE team =’a’ ORDER BY age DESC) t

          ===============
          Use the query above and let me know also if you found our assistance quite helpful then you can consider for a cup of coffee for us as well 🙂

          Reply
  18. supportgej says:

    please i have been following your tutorials, but i want to get the rank of a particular user, using RANK() equivalent in MySQL

    Reply
      1. supportgej says:

        yes but it is returning the rank as 1, its a if it is treating it as a single request, i want the query to return the rank of that particular user accordingling, lets say it should return the rank of George with age 23 as 8. just in that order.

        thank you

        Reply
  20. Ashish Pandey says:

    First of all thanx for sharing very nice tutorial and great information…
    i just wanted to ask is it possible to use where condition in the query above…
    and if we are using where condition will the query display correct rank, that is should actually display…

    Reply
    1. Amit Sonkhiya says:

      Hi, Ashish
      You can definitely use where clause in query. However the result is always depend on what and how did you write the query.
      You should use this table as sample and test the result first or you can use some dummy data using dummy data generator tools available online.

      Reply
      1. appu says:

        SELECT student_ID, total_s, rank FROM
        (SELECT student_ID, total_s,session,term,class,
        @curRank := IF(@prevRank = total_s, @curRank, @incRank) AS rank,
        @incRank := @incRank + 1,
        @prevRank := total_s
        FROM tb_grade p, (
        SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
        ) r WHERE session=’2016/2017′ and term=’third term’ and class=’jss1 a’ ORDER BY total_s DESC) s

        Reply

Leave a Reply

Enclose a code block like: <pre><code>Your Code Snippet</code></pre>.