#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    4
    Rep Power
    0
    Here is the table: (bowler)

    ID Name Game
    1 Joe 215
    2 Eric 289
    3 Scott 278
    4 Eric 245
    5 Mark 280
    6 Joe 289
    7 Bob 278
    8 Scott 270
    9 Scott 278
    10 Eric 289

    This is the results I need:

    Eric 289
    Joe 289
    Mark 280
    Scott 278
    Bob 278

    The order for names that have the same Game is not important, so for 289 it could be:
    Joe 289
    Eric 289

    I have tried so many things I cant even remember what they all are. I have probably tried some more then once.
    If anyone has any suggestions, please help.

    Eric
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    27
    Rep Power
    0
    try
    "select name,game from bowler order by game"
  4. #3
  5. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    i'm having trouble with this too, i tried:

    SELECT Name, max(Game)
    FROM Bowler
    GROUP BY Name
    ORDER BY Game DESC

    but that gave me:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    +-------+-----------+
    | Name | max(Game) |
    +-------+-----------+
    | Eric | 289 |
    | Mark | 280 |
    | Scott | 278 |
    | Bob | 278 |
    | Joe | 289 |
    +-------+-----------+
    [/code]
    the GROUP BY and ORDER BY clauses don't seem to be working together
  6. #4
  7. 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
    Ryan,

    You need to order by max(game) not game. Alias it to make it easier:

    select name,max(game) as mgame from bowler
    group by name
    order by mgame;
  8. #5
  9. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    oh! thanks rod

    so eric, try:

    SELECT Name, max(Game) AS maxGame
    FROM Bowler
    GROUP BY Name
    ORDER BY maxGame DESC, Name

    and you should get:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    +-------+---------+
    | name | maxgame |
    +-------+---------+
    | Eric | 289 |
    | Joe | 289 |
    | Mark | 280 |
    | Bob | 278 |
    | Scott | 278 |
    +-------+---------+
    [/code]
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    4
    Rep Power
    0
    Thank you so very much Rod and Ryan for the help.
    This worked perfectly.

    If you wish to see this working.
    http://www.mountainlaneswausau.com/
    Click on High Scores for the Year

Similar Threads

  1. order by group count?
    By qwer in forum MySQL Help
    Replies: 1
    Last Post: January 10th, 2004, 06:30 AM
  2. order by in a group
    By Henning in forum MySQL Help
    Replies: 3
    Last Post: December 16th, 2003, 01:48 PM
  3. order by, group by question
    By black_lotus in forum Database Management
    Replies: 6
    Last Post: December 10th, 2003, 03:22 PM
  4. ORDER BY before GROUP BY
    By mimics in forum MySQL Help
    Replies: 5
    Last Post: October 25th, 2003, 03:40 PM
  5. count + join + group by + order by
    By gig745 in forum MySQL Help
    Replies: 3
    Last Post: October 13th, 2003, 10:10 PM

IMN logo majestic logo threadwatch logo seochat tools logo