Gettings rid of duplicates

[TheMagician]

CREATE TABLE `ks_scores` (
  `score_id` int(11) NOT NULL auto_increment,
  `score_user_id` int(11) default NULL,
  `score_team_id` int(11) default NULL,
  `score_gtr_id` int(11) default NULL,
  `score_bass_id` int(11) default NULL,
  `score_drums_id` int(11) default NULL,
  `score_vox_id` int(11) default NULL,
  `score_game_id` tinyint(4) NOT NULL,
  `score_instrument_id` tinyint(4) NOT NULL,
  `score_diff_id` tinyint(4) default NULL,
  `score_song_id` int(11) NOT NULL,
  `score_score` int(11) NOT NULL,
  `score_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`score_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE `ks_scores_opt` (
  `opt_id` int(11) NOT NULL, -- ks_scores.score_id
  `opt_stars` tinyint(4) default NULL,
  `opt_image` varchar(255) collate latin1_general_ci default NULL,
  `opt_video` varchar(255) collate latin1_general_ci default NULL,
  `opt_comment` varchar(255) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`opt_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

Query:

 

SELECT s1.score_song_id, s1.score_score, s1.score_timestamp, opt.opt_stars, opt.opt_image, opt.opt_video, opt.opt_comment
FROM ks_scores s1
LEFT JOIN ks_scores_opt AS opt ON ( s1.score_id = opt.opt_id )
WHERE s1.score_user_id =2
AND s1.score_game_id =3
AND s1.score_instrument_id =1
AND s1.score_diff_id =3
ORDER BY s1.score_song_id ASC

 

Result:

 

score_song_id 	score_score 	score_timestamp 	opt_stars 	opt_image 	opt_video 	opt_comment
1 	266381 	2008-03-03 16:57:02 	5 	NULL 	NULL 	NULL
1 	999999 	2008-11-11 04:00:02 	0 	NULL 	NULL 	NULL
1 	283491 	2008-07-26 19:24:16 	5 	NULL 	NULL 	NULL
2 	154584 	2008-03-03 16:57:30 	5 	NULL 	NULL 	NULL
2 	999999 	2008-11-11 04:01:06 	0 	NULL 	NULL 	NULL
3 	999999 	2008-11-11 04:01:06 	0 	NULL 	NULL 	NULL
3 	228592 	2008-03-03 16:57:49 	5 	NULL 	NULL 	NULL
...

 

Wanted result:

 

score_song_id 	score_score 	score_timestamp 	opt_stars 	opt_image 	opt_video 	opt_comment
1 	999999 	2008-11-11 04:00:02 	0 	NULL 	NULL 	NULL
2 	999999 	2008-11-11 04:01:06 	0 	NULL 	NULL 	NULL
3 	999999 	2008-11-11 04:01:06 	0 	NULL 	NULL 	NULL
...

 

Question is, how do I achieve this?

 

Group by returns arbitrary rows, I've tried that, even when using "ORDER BY s1.score_song_id ASC, s1.score_score DESC"... I believe the problem is called "group-wise aggregate" or "within-group aggregate", but I just can't seem to be able to get the solution working.

[Hybride]

SELECT DISTINCT(whichever row/s you want distinct)

 

[TheMagician]

SELECT DISTINCT(whichever row/s you want distinct)

 

 

Doesn't work. It only sorts the rows again to some arbitrary order (however still ordering by the score_song_id field, but the scores go in different order).

[Hybride]

SELECT DISTINCT (row) returns only one count of the row you want; ORDER BY is still necessary to get it into whatever order you want by whatever row. Example:

SELECT DISTINCT(s1.score_song_id), s1.score_score, s1.score_timestamp, opt.opt_stars, opt.opt_image, opt.opt_video, opt.opt_comment
FROM ks_scores s1
LEFT JOIN ks_scores_opt AS opt ON ( s1.score_id = opt.opt_id )
WHERE s1.score_user_id =2
AND s1.score_game_id =3
AND s1.score_instrument_id =1
AND s1.score_diff_id =3
ORDER BY s1.score_song_id, s1.score_score ASC

Where SELECT DISTINCT would grab only the first instance/occurance of s1.score_song_id, and ordering it and the score by ascending order (A-Z,1-9). DESC would make it Z-A,9-1 (backwards).

 

You can make multiple ORDER BY instances.

[TheMagician]

SELECT DISTINCT (row) returns only one count of the row you want; ORDER BY is still necessary to get it into whatever order you want by whatever row. Example:

SELECT DISTINCT(s1.score_song_id), s1.score_score, s1.score_timestamp, opt.opt_stars, opt.opt_image, opt.opt_video, opt.opt_comment
FROM ks_scores s1
LEFT JOIN ks_scores_opt AS opt ON ( s1.score_id = opt.opt_id )
WHERE s1.score_user_id =2
AND s1.score_game_id =3
AND s1.score_instrument_id =1
AND s1.score_diff_id =3
ORDER BY s1.score_song_id, s1.score_score ASC

Where SELECT DISTINCT would grab only the first instance/occurance of s1.score_song_id, and ordering it and the score by ascending order (A-Z,1-9). DESC would make it Z-A,9-1 (backwards).

 

You can make multiple ORDER BY instances.

 

But it returns the same rows, just in different order. I only want the highest score_score per score_song_id.

[fenway]

Then use a GROUP BY -- DISTINCT is useless.