The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: MySQL - Get position of sorted rows?

  1. #1

    MySQL - Get position of sorted rows?

    I have a table which stores high-scores, along with player ids. I want to be able to extract a record by a players id, and then get the rank, or position of their score in the table.

    "SELECT * FROM `high_scores` ORDER BY `game_score` DESC";

    Will give me the results ordered by score, highest to lowest, but how can I then get a record from it and determine where in the table this score is placed?

  2. #2
    ummm... do what? what do you mean "where in the table the score is placed"?
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  3. #3
    no i believe he is looking for something like if he is in the top 10 best points per game show what rank he is..

    i dont know if this is the most effective way.. but try something like this

    PHP Code:
    Select from whatever table

    $x
    =0
    while($row mysql_fetch_array($result) {
    if (
    $user == $row['user']) {
    echo 
    "$user is rank $x out of " count($result);
    break;
    } else {
    $x++
    }


  4. #4
    I didn't think I'd done a very good job of explaining so I'll see if I can do better.

    A have a table called `high_scores` which, among other things, has columns for 'id', 'score' and 'player_id'.

    id-------score-------player_id
    -----------------------------------
    0-------1500-------54
    1-------2800-------222
    2-------200--------11
    3-------5000-------514

    Lets say I want to select the details for the user with player_id=222. I can get his score value, and the id unique to his score entry.

    Or if I want to display all scores and their players in order of highest score to lowest, I can do that as mentioned above.

    But what if I want to say

    "Select the score that is 59th in the score column"

    But not merely the 59th entry in my table; rather 59th down the list of ORDERED scores.

    Basically I want to be able to say "you are in Nth" position, purely based on the players score against all other scores.

  5. #5
    Quote Originally Posted by duncanhall View Post
    I didn't think I'd done a very good job of explaining so I'll see if I can do better.

    A have a table called `high_scores` which, among other things, has columns for 'id', 'score' and 'player_id'.

    id-------score-------player_id
    -----------------------------------
    0-------1500-------54
    1-------2800-------222
    2-------200--------11
    3-------5000-------514

    Lets say I want to select the details for the user with player_id=222. I can get his score value, and the id unique to his score entry.

    Or if I want to display all scores and their players in order of highest score to lowest, I can do that as mentioned above.

    But what if I want to say

    "Select the score that is 59th in the score column"

    But not merely the 59th entry in my table; rather 59th down the list of ORDERED scores.

    Basically I want to be able to say "you are in Nth" position, purely based on the players score against all other scores.
    switch the above code to something like this

    PHP Code:
    $x=0
    while($row mysql_fetch_array($result) {
    if (
    NthPerson == $x) {
    echo 
    "You have selected the Nthperson;
    break;
    } else {
    $x++
    }


  6. #6
    Thanks for the reply, but I was rather hoping for a MySQL solution. Correct me if I'm wrong, but if my table held 10,000 high scores then putting that into a PHP loop might be pretty intensive compared to a nice MySQL query.

    Also, as well as saying "Select the score which is rank N"

    I would like to be able to get a 'rank' given a particular score.
    Last edited by duncanhall; June 1st, 2007 at 12:45 PM.

  7. #7
    has to be done via server-side code... can't be done w/ just mySQL the way rschoenbach and x would be defined as the players' rank
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  8. #8
    Ok, so either my explanation is still pretty useless, or it's simply not possible.

    My MySQL knowledge is pretty rudimentary at best, but:

    I know you can sort tables by specific criteria.
    I'm 'fairly sure' you can create tables on the fly for query purposes.

    So then, ignoring everything else, what if my question was just this:

    How can I :

    Select everything from table A, ordered by a certain column.
    Create a temporary table B and insert table A's ORDERED content into it
    Query table B (I'm assuming the row indexes will now be equal to the order specified by the first query in step 1).

    This still sounds overly complicate but I don't seem to be getting anywhere with this.


    Edit: Just read above. Fair play, I guess I shall concede, but it still feels like it should be possible.

  9. #9
    I know u can do temp tables with SQL not sure if it is possible with mySQL but it would be the best way to do it with a temp table.

  10. #10
    There might be another way to do it.

    If you sort a table based on whatever you could use the limit command to start at a specific record.

    Like

    Select * from table Limit 10,20

    would start at record 11 and show upto record 30.

    In this example you could make Nth be the limit to start at.

  11. #11
    Your correct dunchanhall it can be done with sql, it's an annoyance in mySQL tho because it does not have a handy row number function available for counting the rows of a query, so we have to do it the hard way.

    Create a derived table that has the rank for each score and query that, logically the rank is the top down count of an ORDER BY score DESC query on a table like the following...
    Code:
    id  score   player_id
    1   1000    4
    2   2000    3
    3   3000    2
    4   4000    1
    We want to know what is ranked 3rd (we know its id is 2 and score is 2000 at a glance), to do that in mySQL we count the rows, store that count as a new column called rank and query the new table by rank.

    Code:
    SET @rownum := 0; # create the variable
    
    SELECT * FROM (
      SELECT @rownum := @rownum+1 AS rank, id, score, player_id # increment the row count and store as rank for this result
      FROM scores 
      ORDER BY score DESC
    ) AS derived_table WHERE rank = 3; # AS derived_table is the alias for the new derived table!
    would return

    Code:
    rank   id  score   player_id
    3  	2  2000    3
    If this dosnt make sense let me know and 'll break the SQL down more, I tested this on mySQL 5.0

  12. #12
    ^^ wow... didn't know that... though it looks pretty ugly to do.
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  13. #13
    its far from ugly and cuts a lot of server side nonsense out, its basically just a nested query if you look at it closely, the AS statements are aliases for the things we create (rank and derived table), @rownum is just incremented when your running the standard query and stored as rank in the new table, then you query that.

  14. #14
    supposed the table is ordered `score` desc, `id` asc


    get the 10th rank from the top
    PHP Code:
    "select * 
    from `scoretable` 
    order by `score` desc, `id` asc 
    limit 9, 1"


    get rank number by a given score 2000
    PHP Code:
    "select count(*)+1 as rank 
    from `scoretable` 
    where `score` > 2000"
    ;
    // what if 2000 is non-existent, is it still considered to have a rank number 

    ramie's reply uses multi-query which requires php 5. that's fine when you design the app for own use. but if you are making an app for other ppl to use, lowered the requirement to php 4.4, mysql 4.1 or something like that.

    assume there is no function rank() in mysql that will do
    select *, rank() from scoretable order by score desc, id asc
    this is what i came up. everything is in a single sql statement.


    get rank number by a given scoreid 123, assume possible identical scores
    PHP Code:
    "select a.rank+b.rank as rank
    from (SELECT count(*)+1 as rank FROM scoretable where score > (select score from scoretable where id = 123)) as a
        straight_join
        (SELECT count(*) as rank FROM scoretable where score = (select score from scoretable where id = 123) and id < 123 ) as b" 


    if there is a better solution i would love learn

  15. #15
    Awesome, thanks for the help guys, I knew there was a more sensible way of doing things. I wont be able to work on this on until Wednesday, but eirches example seems to make perfect sense (I'm resticted to PHP 4).

    Cheers!

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012