Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    4

    Unanswered: Help with grouping records / distinct records

    Hi there - been a member of this forum for ages, but this is the first time I've posted looking for help - most of the time I get what I'm looking for by searching.

    What I have is a table full of record times. A player can have more than one record, and this query gets the information I need perfectly:

    Code:
    SELECT records.player_id, records.record_id, records.game_id, game_modes.description as mode_description, records.record_time, players.firstname, players.lastname, players.nickname, players.country, teams.team_title, classes.class_title, tracks.track_title, games.game_title, games.version, record_types.description as type_description
    FROM records, games, players, classes, teams, tracks, record_types, game_modes
    WHERE 
    games.game_id = 1
    AND records.mode_id = 1
    AND records.track_id = 1
    AND records.class_id = 1
    AND records.type_id = 1
    AND records.track_id=tracks.track_id
    AND records.player_id = players.player_id 
    AND records.team_id = teams.team_id
    AND records.class_id = classes.class_id
    AND records.game_id = games.game_id
    AND records.type_id = record_types.type_id
    AND records.mode_id = game_modes.mode_id
    ORDER BY records.record_time ASC
    LIMIT 10
    Now, what I want to do is give my site visitors the option of selecting only the best times from each player. Can anyone point me in the right direction of how to modify this query to get the result?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the ORDER BY and LIMIT, add a semicolon at the end, and put this in front of the SELECT --
    Code:
    CREATE TEMPORARY TABLE playertimes
    now the query will save all player times, so all you need is a second query --
    Code:
    select player_id
         , record_id
         , game_id
         , mode_description
         , record_time
         , firstname
         , lastname
         , nickname
         , country
         , team_title
         , class_title
         , track_title
         , game_title
         , version
         , type_description
      from playertimes as T
     where record_time
         = ( select max(record_time)
               from playertimes
              where player_id = T.player_id )
    without the temp table, you could use your original query as a subquery for playertimes, but that means sticking it into the second query in two places
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2005
    Posts
    4
    Thanks very much for that - I'll give it a try.

  4. #4
    Join Date
    Apr 2005
    Posts
    4
    Hello again. Well, it turns out I made a bit of a stupid assumption. Although my friend and I have a dedicated server, and always upgrade things like php, it turns out we have no control over our MySQL installation and it's version 4.0.20.

    So the query that I came up with, which was working perfectly, doesn't work on the server installation.
    Code:
    SELECT 
    records.player_id, 
    records.record_id, 
    records.game_id, 
    records.updated, 
    game_modes.mode_title, 
    records.record_time, 
    players.firstname, 
    players.lastname, 
    players.nickname, 
    players.country, 
    teams.team_title, 
    classes.class_title, 
    tracks.track_title, 
    games.game_title, 
    games.version, 
    record_types.type_title
    FROM games, records, classes, teams, tracks, record_types, players, game_modes
    WHERE 
    	records.record_time = (
    		SELECT MIN(record_time) 
    		FROM records 
    		WHERE records.player_id = players.player_id 
    		AND records.game_id = 1
    		AND records.mode_id = 1
    		AND records.track_id = 1
    		AND records.class_id = 5
    		AND records.type_id = 1
    		GROUP BY records.player_id)		
    	AND	
    			
     records.track_id=tracks.track_id
    AND records.track_id = 1
    AND records.player_id = players.player_id 
    AND records.team_id = teams.team_id
    AND records.class_id = classes.class_id
    AND records.game_id = games.game_id
    AND records.type_id = record_types.type_id
    AND records.mode_id = game_modes.mode_id			
    ORDER BY records.record_time ASC
    LIMIT 5
    I understand I'd need to do this in two queries in MySQL 4, but I'm not sure of the steps. Can anyone help me break this down to work on MySQL 4?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create temporary table min_record_times
    select records.player_id
         , min(records.record_time) as min_record_times
      from records 
     where records.game_id = 1
       and records.mode_id = 1
       and records.track_id = 1
       and records.class_id = 5
       and records.type_id = 1
    group 
        by records.player_id
    ;
    select records.player_id
         , records.record_id
         , records.game_id
         , records.updated
         , game_modes.mode_title
         , records.record_time
         , players.firstname
         , players.lastname
         , players.nickname
         , players.country
         , teams.team_title
         , classes.class_title
         , tracks.track_title
         , games.game_title
         , games.version
         , record_types.type_title
      from records
    inner
      join games
        on games.game_id = records.game_id 
    inner
      join classes
        on classes.class_id = records.class_id 
    inner
      join teams
        on teams.team_id = records.team_id 
    inner
      join tracks
        on tracks.track_id = records.track_id
    inner
      join record_types
        on record_types.type_id = records.type_id 
    inner
      join players
        on players.player_id  = records.player_id 
    inner
      join game_modes
        on game_modes.mode_id = records.mode_id 
    inner
      join min_record_times
        on min_record_times.player_id = records.player_id  
       and min_record_times.min_record_time = records.record_time
     where records.track_id = 1
    order 
        by records.record_time asc limit 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2005
    Posts
    4
    Thanks again for your help with this.

Posting Permissions

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