Welcome to the OzoneAsylum FaqWiki
Frequently Asked Questions
Server Side Coding

How do I find the maximum value of a grouped field? Pages that link to <a href="https://ozoneasylum.com/backlink?for=5774" title="Pages that link to How do I find the maximum value of a grouped field?" rel="nofollow" >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:

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


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

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

« BackwardsOnwards »

Show Forum Drop Down Menu