SQL (php) Need to Extract Best Records

Question

The following code is not working as it should.

SELECT Species, FishName, Rank, Angler, lb, oz, dr, Drams, Peg, DateCaught
FROM 
     (SELECT Species, FishName, Rank, Angler, lb, oz, dr, Drams, Peg, DateCaught 
      FROM   ConqPB 
      ORDER BY FishName, Drams DESC) a
WHERE Species = 'Common Bream' 
GROUP BY FishName 
ORDER BY Species ASC, Rank ASC, Drams ASC

It is a list for the species of Common Bream and will show a record list of the top weights for each fish name AND the angler that caught them.

BUT for some unknown reason - and I haven't edited it, it has stopped working and now only returns the one anglers best rather than the actual best.

The inner statement has tested fine and returns a list of the fish names and arranged the weight (drams) in desc order. When this is displayed then the top record of each fish name is the highest capture weight. The outer statement is now failing.

Any help here would be really appreciated. Also I would love to know why it stopped working.

An example:

Species           FishName               Drams             Angler  
Common Bream      Henry                  300               Peter Beard  
Common Bream      Henry                  400               Harry Web  
Common Bream      Henry                  0                 Carl Pilk  
Common Bream      Darcy                  0                 Peter Beard  
Common Bream      Darcy                  760               Harry Web  
Common Bream      Darcy                  900               Carl Pilk 

The result would be

Common Bream Records

Fishname             Drams                 Angler  
Henry                400                   Harry Web  
Darcy                900                   Carl Pilk 

Answer 1

select
*
from
t
inner join (
  select
  species, fishname, max(drams) as maxdrams
  from
  t
  group by species, fishname
) sq on t.species = sq.species and t.fishname = sq.fishname and sq.maxdrams = t.drams

• live demo