Query to obtain rank function in MySQL

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

  • Ashish Pandey

    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…

    • Amit Sonkhiya

      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.

  • anon

    I am inclined to avoid these approaches using user variables for the reasons mentioned here: http://rpbouman.blogspot.com/2009/09/mysql-another-ranking-trick.html

  • supportgej

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

    • Amit Sonkhiya

      Hey,

      Did you try to add WHERE clause in out most SELECT?

      WHERE userid = 1 ORDER BY age) s

      • supportgej

        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

        • Amit Sonkhiya

          Hi,

          I have edited my previous reply and corrected the query. Please run the newer and let me know.

  • supportgej

    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

    • Amit Sonkhiya

      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

      • supportgej

        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.

        • Amit Sonkhiya

          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 🙂

  • Ali

    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?

    • Amit Sonkhiya

      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

  • Fujimaru R

    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

    • Amit Sonkhiya

      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

  • anil kumar

    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

  • Carolina M

    very useful, thanks

  • Kranthi Kumar

    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.

    • Kranthi Kumar

      Please try without “set” keyword

      ex:
      set @pk1 =”;
      set @rn1 =1;
      set @sal =”;

      • Amit Sonkhiya

        Hi Kranthi,

        I have dropped you an email regarding this from support@fellowtuts.com. Please check back & let me know.

        • Kranthi Kumar

          Thanks Sonkhiya …. :). I will check and let you know.

          Special Thanks for quick response …….. 🙂

          • Genesis

            Hi Krathi Kumar. I have same problem with you. May you please teach me how you sold that? Thank you.

        • Kranthi Kumar

          I sent you reply behalf of your response
          looking forward

        • Genesis

          Hi Amit Sonkhiya i have same problem with this.
          Can you please help me.
          This is my email.

          Thank you.

          • Amit Sonkhiya

            Hello @Genesis

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

          • Genesis Bertiz

            Here Sir. Event Rank is base on the Number of Events. And the Cost Rank is base o the Cost. How can i get the rank of these? Thank you sir in advance. https://uploads.disquscdn.com/images/898800640a3c7dbede9c54eeb547f0ebddfd69b60a703dbb712362d0ea343c6c.png

          • Genesis Bertiz

            How can i get the rank like that in php mysql? Thank you Sir.

          • Amit Sonkhiya

            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

          • Genesis Bertiz

            @amitsonkhiya:disqus Thank you sir!

  • Nana Akomeah

    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!

    • Amit Sonkhiya

      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

      • Nana Akomeah

        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?

        • Amit Sonkhiya

          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.

      • Nana Akomeah

        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!

  • jaymit ghadiya

    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….

    • Amit Sonkhiya

      Hi jaymit

      You need to use subqueries. Mail us your sample database script and expected output at support@fellowtuts.com so that we can assist you further

      Thanks

  • Andres Perez

    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!

    • Amit Sonkhiya

      Hello @disqus_tmAcURjdaR:disqus

      Are you surely referring to Jaymit Ghadiya? If you wish to take our support, please email us support@fellowtuts.com along with sample database script and expected output.