How do I find the maximum value of a grouped field?

Say you have league table recording the positions of the rankings of each player over time but you want to find the most recent rank for each player. It can get quite complex and messy as you need to use GROUP BY and some work arounds for this require temporary tables and truly scarey lookin SQL. The trick (takn from Csaba Gabor's comments in the MySQL manual) involves joinging the table to itself and then going through it (with the < ) until there are no more date values (i.e. you have found the highest date). It would work something like this:

SELECT *

            FROM player_ranking AS pr

       LEFT JOIN player_ranking AS pr2

              ON pr.player_id = pr2.player_id

             AND pr.date < pr2.date

           WHERE pr2.player_id IS NULL

        GROUP BY urr.player_id         

        ORDER BY urr.rank

It is a powerful and useful piece of SQL - the problem might never arise but if it does this is the most elegant solution.

---------------------------
Relevant links:

3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

____________________
Emperor

(Added by: Emperor on Thu 31-Jul-2003)

(Edited by: Emperor on Thu 31-Jul-2003)