SQL: Return only the first row of each Group By
Posted on 2003-03-10
I'm working on a db that tracks puppy information, and need a query that will return a list of medications due for each puppy (to feed a report obviously).
However, the problem I am having is that it will return EVERY next due medication regardless of whether it has already been given. I only need it to list the last row of the matches for each puppy (or first - depending how it's sorted).
For example, the query I have now will return 3 rows for the puppy "Patriot":
DogID MedName LastOfDateGiven NextDue NextMedName
Patriot DHPP1 4/22/2002 6/3/2002 DHPP2
Patriot DHPP2 5/30/2002 7/11/2002 DHPP3
Patriot DHPP3 6/27/2002 6/26/2003 DHPP-B
However, the only row I want to see is the last one (but for every dog in the query, not just this one) because the first two's "NextDue" have already been filled. I only ever care about the medication given on the most recent date for a puppy.
I tried using the Last() function on the "DateGiven" field, but it still shows all of them. I'm guessing it's because either the other fields differ, or because I haven't specified the it needs to be the last within the DogID group, not its own group. But I don't know how to define that.
BTW, if I remove any of the fields in the Group By section, Access complains that I "tried to execute a query that does not include the specified expression '(whatever I removed)' as part of an aggregate function." It doesn't make sense to me that I should have to group by every field. If anything, just by DogID. But then, I don't fully understand aggregate functions yet.
Anyway, here is the query that returned the rows from which the above sample was excerpted:
SELECT SchedMeds.DogID, SchedMedTypes.MedName, Last(SchedMeds.DateGiven) AS LastOfDateGiven, DateAdd("ww",[SchedMedTypes]![WeeksGood],[SchedMeds]![DateGiven]) AS NextDue, SchedMedTypes_1.MedName
FROM ((SchedMedTypes LEFT JOIN SchedMedTypes AS SchedMedTypes_1 ON SchedMedTypes.NextMed = SchedMedTypes_1.MedTypeID) RIGHT JOIN SchedMeds ON SchedMedTypes.MedTypeID = SchedMeds.MedTypeID) RIGHT JOIN PuppyPgm ON SchedMeds.DogID = PuppyPgm.DogID
GROUP BY SchedMeds.DogID, SchedMedTypes.MedName, DateAdd("ww",[SchedMedTypes]![WeeksGood],[SchedMeds]![DateGiven]), SchedMedTypes_1.MedName, SchedMedTypes.WeeksGood, PuppyPgm.ExitInfo, PuppyPgm.ExitDate
HAVING (((SchedMedTypes.MedName) Like "DHPP*") AND ((SchedMedTypes.WeeksGood) Is Not Null) AND ((PuppyPgm.ExitInfo) Is Null) AND ((PuppyPgm.ExitDate) Is Null))
ORDER BY SchedMeds.DogID, Last(SchedMeds.DateGiven);
There must be a better way to present this question, but I wanted to include the full query just to be sure the whole picture is available.
I think what it comes down to is this... If there is a table of Dogs related one-to-many with a table of Medications, I need a way to return only the rows in Medications that have the most recent DateGiven for each Dog.
I can do it for a single dog just fine. But I need the query to return the results for every dog in the database. That's the power of queries, right? It would be silly to run the query 200 times to get this result. There must be a better way.
Hopefully the concept of what I'm trying to do can be clear without fully understanding the data behind it, but let me know if I should provide information about the database design, such as the tables feeding this query.