Thread: Ranking Rows

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    14
    Let's say I have a list of names and a list of scores. How do I rank these list of names by score? Is it possible to tell somoone that they are tied in 8th place, or could I only count the #of rows theyare ie If they are tied for 8th, but the 11th row listed they'll be listed as 11th?

    Thanks,

    Weldon
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    There really isn't any way to account for ties in a ranking using just MySQL. You'll have to pull the records in a query order by the field that you are ranking them on, then use a script to sort thru them to get a ranking allowing for ties. What scripting language are you using?
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Aalborg, Denmark
    Posts
    9
    Rep Power
    0
    ...but he can ease the proces a lot using MySQL:

    SELECT names FROM table SORT BY score

    Depending on your scripting language, you would now pull an array out of the database containing the names sort by score.
    Then do a loop that stops when it reaches the relavant name and print the iteration number. In php, it would look like this:

    for ($i = 1; $array[$i] != $relavant_name, $i++) {
    }
    echo $relavant_name . " is on the " . $i . "th place.";

    Correct me if I'm wrong...
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    14
    Rep Power
    0
    Try something like this to get a sort by scores and look at the COUNT(*) values > 1

    SELECT names, score FROM table GROUP BY score;

    jcbell@magnolia.net

    ------------------

Similar Threads

  1. 5 table join returning too many rows of duplicate data
    By DC Dalton in forum PostgreSQL Help
    Replies: 13
    Last Post: August 14th, 2003, 01:31 AM
  2. Hiding/showing specific table rows
    By oscarBravo in forum HTML Programming
    Replies: 1
    Last Post: August 5th, 2003, 06:23 PM
  3. Replies: 2
    Last Post: March 4th, 2003, 05:05 PM
  4. Counting amount of rows for a field within mysql query
    By strange1 in forum PHP Development
    Replies: 5
    Last Post: December 27th, 2002, 08:24 AM
  5. Replies: 1
    Last Post: March 6th, 2002, 10:20 PM

IMN logo majestic logo threadwatch logo seochat tools logo