How do I find the maximum value of a grouped field? — OZONE Asylum, home of the Mad Scientists

FAQ

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

This page's ID: 5774

You are editing "How do I find the maximum value of a grouped field?"

Who can edit an FAQ?	Anyone registered may edit an FAQ.

Your User Name:
Your Password:
Login Options:	Remember Me On This Computer

Your Text: Insert Slimies » Insert UBB Code » Close Last Tag \| All Tags UBB Help	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: [code] 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[/code] 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: [url=http://www.mysql.com/doc/en/example-Maximum-column-group-row.html]3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field[/url] ____________________ [internallink=4626]Emperor[/internallink] [small][i](Added by: [url=http://www.ozoneasylum.com/cgi-bin/ubbmisc.cgi?action=getbio&UserName=Emperor]Emperor [/url] on Thu 31-Jul-2003)[/i][/small] [small][i](Edited by: [url=http://www.ozoneasylum.com/cgi-bin/ubbmisc.cgi?action=getbio&UserName=Emperor]Emperor [/url] on Thu 31-Jul-2003)[/i][/small] Loading...
Options:	Enable Slimies Enable Linkwords

« Backwards — Onwards »

Show Forum Drop Down Menu