PHP(SQL) Script using GROUP by does not display some fields correctly

Question

I am reasonably new to PHP but have been stuck on this for days. Basically, I have a fishing website that has two databases. One is for personal bests and one for all time records. Now, I thought it would be simpler to have just the one table, with everyones personal best and then I could use a PHP/SQL script to retrieve the all time records.

Problem is, it retrieves the name of the fish and and highest weight caught but yet always displays the one person, not the correct person who caught it.

Table consists of the fields basically like Species, FishName, Rank, Weight (Drams), Angler, Peg, DateCaught.

The SCRIPT is

$query = "SELECT Type, Name, Rank, Person, MAX(Drams) as Drams FROM table GROUP BY Name  ORDER BY Type ASC, Rank ASC"; 

$result = mysql_query($query) or die(mysql_error());
echo "<b>Testing a display of All Time Records from PB Table.</b> <br>";

while($row = mysql_fetch_array($result)){
echo "Records for ". $row['Type']. " with name ". $row['Name']. " weighs ". $row['Drams']. " caught by ". $row['Person'];
echo "<br>";
}
mysql_close($con);

What should happen is a list will now produce showing each Name for a fish and the weight that they were caught, and who caught it as in Example 1 but example 2 is happening instead.

Example 1:

Records for Carp with name Piggy weighs 1024 caught by David Bloggs

Carp with name Flipper weighs 123 caught by Arthur Smith

Example 2:

Records for Carp with name Piggy weighs 1024 caught by David Bloggs

Carp with name Flipper weighs 123 caught by Arthur Smith

Answer 1

My guess is that you are getting the correct MAX(Drams), but that on the GROUP BY Name you are getting the 1st Person data not the Person that has the MAX(Drams)

Here are 2 different ways to accomplish what you are trying to do-

---

This first one has a nestled SELECT that reorders your rows by Name - ASC and Drams - DESC, with the outside doing the GROUP BY Name to get the top record

SELECT Type, Name, Rank, Person, Drams 
FROM (SELECT Type, Name, Rank, Person, Drams FROM table ORDER BY Name, Drams DESC) a
GROUP BY Name 
ORDER BY Type ASC, Rank ASC

Here is how this one works-
1- The nestled SELECT runs first, and reorders the table rows by Name (A-Z) and Drams (High-Low). This organized table is then temporarily stored as table a.

(SELECT Type, Name, Rank, Person, Drams FROM table ORDER BY Name, Drams DESC) a

2- The outside SELECT now take table a, and executes the GROUP BY Name, giving us the first (or top/highest) record for each Fish Name, and orders the results by Type (A-Z) and Rank (Low-High).

SELECT Type, Name, Rank, Person, Drams 
FROM  a  /* table a was set in step 1 */
GROUP BY Name 
ORDER BY Type ASC, Rank ASC

---

The second one has a nestled SELECT that gets the MAX(Drams), and then JOINs it to the row with the top record

SELECT table.Type, table.Name, table.Rank, table.Person, table.Drams
FROM table
JOIN ( SELECT Name, MAX(Drams) AS Drams FROM table GROUP BY Name) maxDrams
ON table.Name = maxDrams.Name 
AND table.Drams = maxDrams.Drams
ORDER BY Type ASC, Rank ASC

Here is how this one works-
1- The nestled SELECT runs first, and selects each Fish Name (via the GROUP BY Name) and its MAX(Drams). This organized table is then temporarily stored as table maxDrams.

(SELECT Name, MAX(Drams) AS Drams FROM table GROUP BY Name) maxDrams

2- The outside SELECT now take table maxDrams, and does a JOIN with the original table - table. It finds the row(s) in table that has Name and Drams that == as the rows in maxDrams, and orders the results by Type (A-Z) and Rank (Low-High).

SELECT table.Type, table.Name, table.Rank, table.Person, table.Drams
FROM table
JOIN maxDrams  /* table maxDrams was set in step 1 */
ON table.Name = maxDrams.Name 
AND table.Drams = maxDrams.Drams
ORDER BY Type ASC, Rank ASC

---

You can see an example at - http://sqlfiddle.com/#!2/b6b66/7. This shows both examples with the nestled querys executed first, and then the whole querys executed. Hope this makes it a little clearer.