SQL Query Problem
-
I have two tables Animal and AnimalClassification joined by a forgein key in the AnimalClassification table called HerdBookNumber. I want to count animals that have a given sire (paramSire) in each of six class types (AnimalClassification.FinalClass). The problem is that each animal could have several enteries in the AnimalClassification table and I only want the entry with the highest Lactation Number (AnimalClassification.LactationNumber) for each animal. The query below counts each entry in the AnimalClassification table not just the one with the highest lactation nubmer. NOTE: It is not the highest LactationNumber in the AnimalClassification table but the highest LactationNumber in the AnimalClassification table for the associated animal. SELECT COUNT(Animal.HerdBookNumber) AS Daughters, AnimalClassification.FinalClass FROM Animal INNER JOIN AnimalClassification ON Animal.HerdBookNumber = AnimalClassification.HerdBookNumber WHERE (((Animal.Sex)=1) AND Animal.Sire = paramSire) GROUP BY Animal.Sire, AnimalClassification.FinalClass; Could you please suggest how to get the information I require? Thanks
-
I have two tables Animal and AnimalClassification joined by a forgein key in the AnimalClassification table called HerdBookNumber. I want to count animals that have a given sire (paramSire) in each of six class types (AnimalClassification.FinalClass). The problem is that each animal could have several enteries in the AnimalClassification table and I only want the entry with the highest Lactation Number (AnimalClassification.LactationNumber) for each animal. The query below counts each entry in the AnimalClassification table not just the one with the highest lactation nubmer. NOTE: It is not the highest LactationNumber in the AnimalClassification table but the highest LactationNumber in the AnimalClassification table for the associated animal. SELECT COUNT(Animal.HerdBookNumber) AS Daughters, AnimalClassification.FinalClass FROM Animal INNER JOIN AnimalClassification ON Animal.HerdBookNumber = AnimalClassification.HerdBookNumber WHERE (((Animal.Sex)=1) AND Animal.Sire = paramSire) GROUP BY Animal.Sire, AnimalClassification.FinalClass; Could you please suggest how to get the information I require? Thanks
Something like this might work:
SELECT COUNT(a.*) AS Daughters, ac.FinalClass FROM Animal a INNER JOIN AnimalClassification ac ON (a.HerbBookNumber = ac.HerbBookNumber) WHERE (ac.LactationNumber = (SELECT MAX(LactationNumber) FROM AnimalClassification WHERE HerbBookNumber = a.HerbBookNumber)) AND (Animal.Sex = 1) AND (Animal.Sire = paramSire) GROUP BY ac.FinalClass