Filtering SQL results

[unknown101]

Hi Guys, I have the following query:

 

$highest_avg=mysql_query("SELECT * FROM track_tbl where Artist_ID='$get_artist_ID'") or die ('Error: '.mysql_error ());

$theresult = mysql_query($highest_avg);					
	while ($row = mysql_fetch_array($highest_avg))				
	{
                          //Pull any any details needed		
	$gettherating = $row['Average_rating'];
                          }

 

This query returns the artist_id, all their tracks and Track_IDS plus the current average rating for each (in the field Rating).  Now from the result I want to be able to select the highest rated from each artist and output this (so essentially only returning a single track from each seperate artist).  So for example I have a table like:

 

Artist ID  || Track_ID  ||  Rating

1                  1                4

1                  2                6

 

Obivouly both are from the same artist, but they have 2 different tracks.. I want to select the highest rated in this case Track 2.

 

I have tried to use the MAX function in my sql query but it doesnt seem to be working:|  It just returns the first record. Im guessing I could add to the query above and do it all in one, but im still learning php/sql so having a few problems.  Could anyone give me some pointers or advice,

 

Thanks in advance

[Barand]

try

SELECT t.artist_id, t.track_id, t.rating
FROM track_tbl t
JOIN (SELECT artist_id, MAX(rating) as highest
        FROM track_tbl GROUP BY artist_id) as x 
        ON t.artist_id = x.artist_id AND t.rating = x.highest