Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    6
    Rep Power
    0

    MySQL Rank Function ?


    I am looking for an aggregate function similar to count to return the rank of a grouped set. What I mean by rank is the relationship of the count of the group to the total group.

    An example would be:

    SELECT COUNT(*) AS Rows, RANK, Manufacturer FROM Cars GROUP BY Manufacturer

    If the result was

    Rows Rank Manufacturer
    10 2 Audi
    20 1 Honda
    5 3 Kia

    I would like to be able to order by any column but still have the rank mean something.

    I hope my explanation was helpful and someone can help me.

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Code:
    SELECT COUNT(*) AS Rows
         , ( select count(*)+1
               from (
                    select Manufacturer
                         , count(*) as c
                      from Cars
                    group
                        by Manufacturer
                    ) as m
              where c >
                    (
                    select count(*) 
                      from Cars
                     where Manufacturer = T.Manufacturer
                    )
           ) AS RANK
         , Manufacturer 
      FROM Cars AS T
    GROUP 
        BY Manufacturer
    ORDER
        BY RANK

    Comments on this post

    • jcarouth agrees : continually amazed, rudy! nicely done.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    6
    Rep Power
    0
    thanks man definitely appreciated
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    1
    Rep Power
    0

    shaka


    hi
    i am having a similar problem.
    i have a table having students and marks. and i want to have a sql query which will return all the student with their marks and their position.
    e.g

    student marks rank
    john 50 2
    mary 67 1


    thanks
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Code:
    SELECT student
         , marks
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE marks > t.marks ) AS rank
      FROM daTable AS t
    ORDER
        BY rank DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by r937
    Code:
    SELECT COUNT(*) AS Rows
         , ( select count(*)+1
               from (
                    select Manufacturer
                         , count(*) as c
                      from Cars
                    group
                        by Manufacturer
                    ) as m
              where c >
                    (
                    select count(*) 
                      from Cars
                     where Manufacturer = T.Manufacturer
                    )
           ) AS RANK
         , Manufacturer 
      FROM Cars AS T
    GROUP 
        BY Manufacturer
    ORDER
        BY RANK
    Very nice solution, but that does not seem to work efficiently for larger tables.

    I tried this with a mid-sized table (700,000 rows) and that took ages (I cancelled the statement after 10 minutes of 100% CPU on computer)

    So I was looking for a different solution (out of curiosity), and finally came up with this statement, which only takes about 2 seconds on my table (compared to the 10minutes that I cancelled)
    Code:
    SELECT @rownum:=@rownum + 1 as rank, 
           num_orders, 
           Manufacturer
    FROM 
    (
      SELECT COUNT(*) AS num_orders, Manufacturer
         FROM Cars t
      GROUP BY Manufacturer
      ORDER BY 1 DESC
    ) t,
    (SELECT @rownum := 0) r
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    nice one, shammat

    just out of curiosity, when did you try my solution? i posted it over a year ago, but you're only replying now???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by r937
    just out of curiosity, when did you try my solution? i posted it over a year ago, but you're only replying now???
    I tried it today.
    The thread showed up as unread for me when you posted the second solution.
    I didn't realize it was that old
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    14
    Rep Power
    0
    Hi,
    I am having a similar problem:
    I am a developer of an online game in ASP, in which the users have a certain amount of XP. What I want is to sort the players by XP and retrieve the rank for a certain user id without using any ASP coding.

    I have this query which does the job:
    Code:
    SET @row =0; 
    SELECT rk FROM 
           (SELECT @row:=@row+1 rk, uRank.* FROM 
                   (SELECT uID, SUM(tblbase.basXP) AS uXP 
                           FROM tbluser INNER JOIN tblbase ON tbluser.uID = tblbase.basUserID 
                                GROUP BY tblbase.basUserID 
                                      ORDER BY uXP DESC
                   ) AS uRank
           ) AS query 
           WHERE query.uID = 5
    (Updated with proper tabulation)

    This works fine. However, this does not take into account that multiple players might have the same amount of XP, and hence should have the same rank.
    I was thinking of a GROUP BY uRank.uXP but that wouldn't work as user id's would disappear too.

    Do you have any idea of how I can achieve this?

    Thank you,
    Adam
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I got a bit lost in that one. Can you provide a small but representative dataset, together with the resultset you'd expect from your query. (Assume, for now, that you want all the columns from the subquery)
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    14
    Rep Power
    0
    I would like a result like this:

    |rk|
    -----
    534

    Just the rank number for a specific player. Which works, but the rank is incorrect as multiple players might have the same rank.

    Just the subquery, not searching for a specific user would return something like this:
    Code:
     rk        uID            uXP 
    ------------------------------
      1          8         411171 
      2         25         353944 
      3        609         248954 
      4         16         237456 
      5       6030         229663 
      6       3873         229184 
      7       1075         217591 
      8        813         216066 
      9        381         184789 
      10       194         118837



    Thank you,
    Adam
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    This from p.697 of The MySQL Cookbook by Paul DuBois:
    http://tinyurl.com/b28cs5
    Code:
    SET @rownum = 0, @rank = 0, @prev_val = NULL;
    SELECT @rownum := @rownum + 1 AS row
         , @rank := IF(@prev_val!=uxp,@rownum,@rank) AS rank
         , @prev_val := uxp AS uxp
      FROM 
         ( 
           YOUR SUBQUERY HERE
         ) t
     ORDER 
        BY uxp DESC;
    Put the query that gives you this resultset between the brackets above.
    Code:
     uID         uXP 
    -------------------
       8         411171 
      25         353944 
     609         248954 
      16         237456 
    6030         229663 
    3873         229184 
    1075         217591 
     813         216066 
     381         184789 
     194         118837
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    14
    Rep Power
    0
    Wow, it almost works! Thank you so much
    Though, it seems Paul DuBois was a little hasty when he wrote the function. Look at his example resultset:

    Code:
    row | rank | score
    1       1       5
    2       2       4
    3       2       4
    4       4       3
    5       5       2
    6       5       2
    7       5       2
    8       8       1
    Look at the last record. The rank drops suddenly from 5 to 8. This is because he retrieves the new rank from the row number, which has increased by three since the last higher score.

    Update: Came up with a working solution to this:
    Code:
    SET @rank = 1, @prev_val = NULL, @prev_rank = NULL;
    SELECT rank FROM
    (
     SELECT @rank := IF(@prev_val!=uXP,@prev_rank+1,@rank) AS rank
         , @prev_val := uXP AS uXP
         , @prev_rank := @rank AS prevRank
         , t.uID
         , t.uName
      FROM 
         (
          SELECT uID, uName, SUM(tblbase.basXP) AS uXP
                 FROM tbluser INNER JOIN tblbase ON tbluser.uID = tblbase.basUserID
                        GROUP BY tblbase.basUserID
    			ORDER BY uXP DESC
         ) AS t
    ) AS showRank WHERE uID = 5
    Not very pretty, but it works.


    One more thing, is it possible to set the counters to 0 in the same query as the ranksearch?

    Like the one posted above, which ends with
    (SELECT @rownum := 0) r

    instead of
    SET @rownum = 0, @rank = 1, @prev_val = NULL;


    Because I am afraid that if many users run the query at the same time, they might set the counters for each others.
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by r243
    Any idea of how this can be solved?
    you should not want this to be "solved"

    let's say you're in a class of students, and the marks are assigned as A, B, C, etc.

    what if you got an A, but were assigned a B because there was already a student who got an A?

    this is unfair, yes? thus, the concept of ties

    this is the same issue that you have here --
    Code:
    rank  score
       1       5
       2       4
       2       4
       4       3
    the two people who got a score of 4 are both in second place, and it would be unfair to give second to one of them and assign third to the other -- it's just wrong

    furthermore, the person who got a score of 3 is not in 3rd place but is in 4th place, because there are 3 people with better scores ahead of him

    helps?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    14
    Rep Power
    0
    Oh but that was why I was asking in the initial question. My ranking system requires multiple players to be able to share the same rank, if their XP is the same. And the solution above works.

    furthermore, the person who got a score of 3 is not in 3rd place but is in 4th place, because there are 3 people with better scores ahead of him
    While that might be true, our current ranking system does not list people like that. And I need the query to agree with the system we have, ie:
    Rank
    ---
    1
    2
    3
    3
    4
    ...etc


    Anyway, thank you

    Any idea about the SET statement?
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo