Ordering using 2 modifiers

[rprincejr]

I am trying to figure out how to get the following results. I want to show 1 result for each year. And each years result should be the team with the highest point total. I have gotten it to display each year, but it shows all teams for each year. I want just the top team.

Heres my code:

$result = mysql_query("SELECT t.team_id,
                t.partner1_firstname AS p1f, t.partner1_lastname AS p1l,
                t.partner2_firstname AS p2f, t.partner2_lastname AS p2l,
                s.sched_id, s.date_y AS year, r.div_id,
                SUM(r.results_numfish) AS numfish,
                SUM(r.results_netwgt) AS netwgt,
                MAX(r.results_bigfish) AS bigfish,
                SUM(r.results_points) AS points
                FROM ".$prefix."_tourn_teams t
                LEFT JOIN ".$prefix."_tourn_results r ON r.team_id=t.team_id
                LEFT JOIN ".$prefix."_tourn_schedules s ON s.sched_id=r.sched_id
                WHERE r.div_id=$division_id
                GROUP BY t.team_id
                ORDER BY year DESC");
        while ($row = mysql_fetch_array($result)) {
			if ($row[year] == date('Y')) {
				echo "$row[year] -<br />";  

			} else {
				echo "$row[year] - $row[p1f] $row[p1l] / $row[p2f] $row[p2l] $row[points]pts.<br />";
			}
		}

Basically I want the query to sort by year showing each years top points team.

[bradgrafelman]

Try adding the year column to your GROUP BY statement, as well as the "points" column to your ORDER BY statement (order in DESCing order, since you want the highest point total to be shown).