Page 1 of 4 123 ... LastLast
Results 1 to 15 of 56
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Position/ranking in ordered data

    Hello,

    Need your help with the following. Not sure exactly whether this can be done with sqls alone...

    Let's say I've data in the table as follows:


    Code:
    username   points
    john         6
    mary         8
    katie        9
    joe          2
    sean         6
    bill         7
    When sorted (ordered by points DESC, then username), the results would appear as:

    Code:
    katie       9
    mary        8
    bill        7
    john        6
    sean        6
    joe         2
    I need a query to select a user and return his username and ranking in the ordered data. For example:

    query mary
    return mary 2

    query sean
    return sean 5

    Is there a way to get to his ranking?

    Currently, I've an sql to do the sorting of the data. I then use a loop with a counter to find out his ranking.

    Could I do it with sqls alone?

    Thanks in anticipation

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Code:
    select
    yt.name,
    yt.points,
       (select count(points) +1
        from yourtable
        where points > yt.points) as rank
    from yourtable as yt
    order by 
    yt.points desc

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by guelphdad
    Code:
    select
    yt.name,
    yt.points,
       (select count(points) +1
        from yourtable
        where points > yt.points) as rank
    from yourtable as yt
    order by 
    yt.points desc
    Thanks, guelphdad

    I'm using MySQL version 4.1.15...I got some errors when I tried your syntax above. Is it because it's not supported?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you got some errors? hmm, let me guess what they might be...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    ... Elephants in the butter dish!

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Just a quick question :
    Does anyone else think that doing a COUNT for each row you a reading is going to be very inefficient when the row numbers start to get very large? I'm trying to understand the efficiency path which the MySQL optimiser might choose with respect to this query.
    Last edited by aschk; 03-28-07 at 07:34.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    depends on the indexes, doesn't it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Very true, in that case would it be an appropriate answer to say that if you index the "points" column the count will return a result rapidly? The other option I was considering would have been to use a VAR in your SQL which you increment per row. However this doesn't help for those who have the same points score. What do you think about this?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    What do you think about this?
    me? i think you should test it and show us the results

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    LOL, that would be great if i had the time. Give me ten minutes...

  11. #11
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Hi guelphdad,

    Sorry, wasn't careful in my first attempts.

    It works beautifully. Many thanks I can now get the sorted records with the rankings added.

    But how do I get the result for a particular user?

    query john
    return john, 4

    Thanks in advance

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    But how do I get the result for a particular user?
    add a WHERE clause:
    Code:
    where yt.name = 'john'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    add a WHERE clause:
    Code:
    where yt.name = 'john'
    Hi r937,

    Thanks! That works

    But when the sorted set is as follows:

    aa 9
    bb 6
    cc 6
    dd 6
    ee 4

    A query for bb, cc, dd gets the ranking of 2, instead of 2 for bb, 3 for cc and 4 for dd.

    Am I missing something?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, i believe you are missing the concept of ties

    6 equals 6 equals 6, so i don't see how you can possibly think that cc is third
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    yup, i believe you are missing the concept of ties

    6 equals 6 equals 6, so i don't see how you can possibly think that cc is third
    Ah I see. But it correctly returns ee with a ranking of 5 (not 3):

    aa 9 (1)
    bb 6 (2)
    cc 6 (2 or 3)
    dd 6 (2 or 4)
    ee 4 (5)

    So I was wondering they must be a 3 and 4 ranking instead of three 2's.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •