SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to output tied rank based on two conditions

    Hi everyone,

    I've searched around for a long time to an answer to this question, but have had no success so far, so I thought I'd finally bite the bullet and ask. What I am trying to do is display tied ranks (i.e. 1=) for when one thing has an equal average score and number of votes to something else (if they have the same average score, but a different number of votes, then the one with the greater amount of votes should be placed in the position higher). I want it to go 1=, 1=, 2, 3=, 3=, 3=, 4 and not 1=, 1=, 3, 4=, 4=, 4=, 7 if you see what I mean, and would ideally like to output the equals sign at the same time as you can see here: http://www.culturerater.com/thelifedrummer

    It currently uses the rather primitive SET @rank =0, SELECT @rank := @rank +1 AS rank method, however the MyCharts pages are able to use the count( DISTINCT (rating) ) +1 method as they are only pulling one user's ratings. I'm thinking this might require the use of a temporary table but am not sure... Anyone got a solution?

    Thanks so much,
    Theo.
    Last edited by theodore; Jun 8, 2009 at 12:31.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by theodore View Post
    I want it to go 1=, 1=, 2, 3=, 3=, 3=, 4 and not 1=, 1=, 3, 4=, 4=, 4=, 7 if you see what I mean
    yes i do

    what you describe is DENSE_RANK, as opposed to RANK -- these are SQL window functions

    unfortunately mysql does not support them

    how are you planning to output this information? if you're using an application language like php, you can just as easily (and probably more efficiently) do this there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Yes I'm using PHP. Do you know the method I could use to do it with that?

    Thanks,
    Theo.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't do php but it's trivial -- loop over the returned result set, compare each row to the next one, and increment the counter as appropriate

    note it might be tricky getting the equals sign to print on the first row of a series of ties

    one can easily do current/previous logic in a loop, but current/next comparisons are a bit harder

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried doing that before but don't know how to do current/previous logic in a PHP loop. Does anyone know how to do this? Sorry, I'm very self-taught!

  6. #6
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again,

    I got an excellent reply over here (http://sitepoint.com/forums/showthread.php?t=620691) and have now got the ranking working how I would like on the main chart pages using PHP (see http://www.culturerater.com). However (*dun dun dun*), I've just realised that a PHP solution is only going to get me so far, because once you click through to a film or a game, etc. on my site (i.e. http://culturerater.com/film/title.php?t=342 - should be =2nd not 4th) it displays the rank again, and this time it is using only mysql (pulling from a temporary table within a stored procedure and counting the rank with this method again: SET @rank =0, SELECT @rank := @rank +1 AS rank).

    Hmm, anyone got any ideas? Am I somehow going to have to do this with PHP?! - Seems fairly inefficient if I do...

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would re-think what's wrong with rank, and why you prefer dense rank, because dense rank is pretty unusual in real life

    this would allow you to get rid of your temp table, too (time to create temp tables is very high overhead)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dense rank has always made more sense to me, and I think it probably suits the website I am creating more. But even if I was using rank, it would still be incorrect with the method I am using above (the Italian Job should be joint 2nd). Is there a better way to calculate rank which I don't know of?

    Thanks, Theo.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    calculating rank is easy -- just count the number of rows which have a lower value and add 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know you're not a PHP person, but I'm thinking I'll just use PHP and test for the correct id in a loop and then output the rank based on that - only thing is that it's going to have to go through the entire loop (although I think you can set it to stop once a certain condition has been reached - can't remember how to do this though). The mysql temporary table I had previously was probably doing something similar in terms of mechanics. But my question is: do you think this is a bad idea? I mean, will it be an inefficient way of doing it/how much less/more efficient will it be to do this, than it would be to get mysql to process it?

    Thanks a lot though. You're all really helping to enlighten me on here. It's hard trying to learn of all this stuff on your own and I should have probably asked more questions sooner rather than leaving it until now.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    theodore, processing the results of the query with php is probably the most efficient approach

    yes, you have to loop over the results, but you don't have to store them all first

    the condition which you would stop at is the last row in the results

    and whether you use dense rank or rank, you can do both inside the loop with just a couple of IF statements and saving the contents of a single row

    k?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot. I actually just finished sorting it out before you posted this. Interesting though, I'd previously been informed that processing everything at database level, and that particularly stored procedures were the best way to go. Is this mysql specific, or is PHP often the most optimum route (have I been misinformed)?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not misinformed as much as not put into the proper context

    do you agree that looping over the rows of the query result set in php is necessary? i mean, you have to print out all the rows, right? typically inside TD html tags, right?

    okay, setting a variable and executing an IF statement within the loop is only microscopic overhead, right?

    so you might think that you could do the ranking either in a stored procedure or in the application layer

    however, the stored procedure would need to use a cursor, and cursors are notoriously slow

    since you can do the ranking just as easily in the application layer, and since it would represent only microscopic overhead because you're going to have to loop in php anyway, then that's where you should do it

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Only thing is, is that on the page I am actually doing this (http://culturerater.com/film/title.php?t=3 or any other film/game page) I don't print out all of the rows - just the rank in the top right.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the query for a single row with its rank is obviously different from the query to list all the rows, each with a rank

    sorry, i thought we were still talking about the 1, 2=, 2=, 2= scenario

    in the query for a single row, use a subquery to count the number of rows with a greater value

    this is where you ~do~ want the database to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK don't worry - I thought that we might be talking about slightly different things. I've obviously got to check whether the combination of both a greater average and number of votes, but could you maybe tell me the SQL for this, because I'm unaware of the syntax. I know it's something like SELECT (SELECT COUNT(> (average && votes than specific row) ) AS rank, etc.

    Probably needs a WHERE clause in there, but WHERE

    Oh maybe it's something like SELECT (SELECT COUNT(film_id) WHERE film_id=in_film_id HAVING > average && votes) AS rank FROM title;

    Or SELECT (SELECT COUNT(film_id) WHERE film_id=1 && > (average && votes FROM title)) AS rank FROM title;

    title is a temporary table (I'm aware that this might not be the best method as you said). I'm pretty confused.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for your table, and indicate what the ranking column is

    also, note that this will be rank, not dense rank -- for dense rank you really do have to sort and retrieve the entire table, just to find the dense rank position
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh right. I mean, what I have working on the site at the moment is dense rank as you can see, and it's working this out through PHP. So if what I have working at the moment is the optimum method, then don't worry. The ranking column was SELECT @rank := @rank +1 AS rank, after setting the variable with SET @rank =0 if that's what you mean?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by theodore View Post
    Oh right. I mean, what I have working on the site at the moment is dense rank as you can see, and it's working this out through PHP. So if what I have working at the moment is the optimum method, then don't worry.
    if it involves reading the entire result set into memory, then it isn't optimum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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