max and count question??

[Skipjackrick]

I have a table where I am counting the number of a particular species in my database.  On my Display page I only want to show the highest value of species for that particular team.

 

Is it possible to use the MAX() script to display the highest "COUNT()" value only?

 

For example,

 

Team A = 4 Red Drum

Team B = 9 Red Drum

Team C = 1 Red Drum

 

My display table would only show Team B with 9 Red Drum

 

Below is my count query for the total counted species of a particular id.

$query_sum = "SELECT species_id, team_id,
	COUNT(species_id)
	FROM submit
	WHERE species_id=1
	GROUP BY team_id"; 

 

But, how would I display only team B.  Here is what I have so far and it displays all of the teams and the total number of that species.

 

while($row = mysql_fetch_array($result))
{
$team_id = $row['team_id'];
$redfish = $row['COUNT(species_id)'];

//get team's name from team table
get_team_name($team_id);

$_details .=<<<EOD
<tr>
	<td align='center'>$teamname</td>
	<td align='center'>$redfish</td>
</tr>
EOD;
}
$_footer ="</table>";

$species_total =<<<SPECIESTABLE
	$_details
	$_footer
SPECIESTABLE;

print $species_total;
?>

 

 

[Skipjackrick]

I figured it out guys.  I just had to insert a LIMIT 1 statement.

 

Thanks anyways.

 

$query_sum = "SELECT species_id, team_id,
	COUNT(species_id)
	FROM submit
	WHERE species_id=1
	GROUP BY team_id
                ORDER BY COUNT(species_id)
                LIMIT 1";