Help with grouping records / distinct records

infoxicated · #1 (**permalink**) 08-06-06, 06:29

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.

r937 · #2 (**permalink**) 08-06-06, 08:27

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

infoxicated · #3 (**permalink**) 08-06-06, 14:13

Thanks very much for that - I'll give it a try.

infoxicated · #4 (**permalink**) 08-31-06, 06:13

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?

r937 · #5 (**permalink**) 09-01-06, 16:22

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

infoxicated · #6 (**permalink**) 09-04-06, 06:53

Thanks again for your help with this.

Internet Services

Web Development

Digital Marketing

Consumer Tech