thwack
asked on
SQL: Return only the first row of each Group By
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",[SchedMedType s]![WeeksG ood],[Sche dMeds]![Da teGiven]) 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",[SchedMedType s]![WeeksG ood],[Sche dMeds]![Da teGiven]), 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.
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",[SchedMedType
FROM ((SchedMedTypes LEFT JOIN SchedMedTypes AS SchedMedTypes_1 ON SchedMedTypes.NextMed = SchedMedTypes_1.MedTypeID)
GROUP BY SchedMeds.DogID, SchedMedTypes.MedName, DateAdd("ww",[SchedMedType
HAVING (((SchedMedTypes.MedName) Like "DHPP*") AND ((SchedMedTypes.WeeksGood)
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.
ASKER
That is what would make the most sense to me, but like I said, when I tried to remove everything else from "GROUP BY" except for Name, it gave the error I quoted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, you would have to do it in 2 steps if you wanted more data displayed than you're grouping on.
ASKER
stiemark, your suggestion sounds like it's on the right track, except that the first query is useless if I can't include the MedTypeID for each row, because that is needed to make the calculations in the second query.
When I do this:
SELECT SchedMeds.DogID, Max(SchedMeds.DateGiven) AS MaxOfDateGiven
FROM SchedMedTypes RIGHT JOIN SchedMeds ON SchedMedTypes.MedTypeID = SchedMeds.MedTypeID
WHERE (((SchedMedTypes.MedName) Like "DHPP*"))
GROUP BY SchedMeds.DogID;
I get almost what I need for the first query. But I can't add the MedTypeID field without getting an error for not using it in an aggregate function.
When I do this:
SELECT SchedMeds.DogID, Max(SchedMeds.DateGiven) AS MaxOfDateGiven
FROM SchedMedTypes RIGHT JOIN SchedMeds ON SchedMedTypes.MedTypeID = SchedMeds.MedTypeID
WHERE (((SchedMedTypes.MedName) Like "DHPP*"))
GROUP BY SchedMeds.DogID;
I get almost what I need for the first query. But I can't add the MedTypeID field without getting an error for not using it in an aggregate function.
You pick up the MedType from the second query.
From my example, the query PuppyMaxDates gives you a list of dogs and their MaxDates. Left-joining this query to SchedMeds on both DogID and DateGiven will then let you pick up the rest of the fields you want while ONLY giving you the records you want.
Mark
From my example, the query PuppyMaxDates gives you a list of dogs and their MaxDates. Left-joining this query to SchedMeds on both DogID and DateGiven will then let you pick up the rest of the fields you want while ONLY giving you the records you want.
Mark
ASKER
The SchedMeds table's primary key uses the 3 fields DogID, MedTypeID and DateGiven. If I left-join on DogID and DateGiven, and there are multiple rows with different MedTypeIDs, it wouldn't know which one it was.
According to your question, if the following records apply to Patriot:
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
, you only wanted to pick up the last one.
So, joining on this with only "Patriot" and "6/27/02" will only give you the record you wanted. Correct?
Remember, for what you're trying to do, you only care about the furthest date for each dog. You don't care what med it deals with (at that point at least).
If you try it, you'll see what I mean.
Mark
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
, you only wanted to pick up the last one.
So, joining on this with only "Patriot" and "6/27/02" will only give you the record you wanted. Correct?
Remember, for what you're trying to do, you only care about the furthest date for each dog. You don't care what med it deals with (at that point at least).
If you try it, you'll see what I mean.
Mark
ASKER
What I haven't mentioned yet is that there are other types of medication. There are also "Bordatella*" and "Rabies*" types. I was just going to handle it with 3 different versions of the query rather than putting the effort into making one perfect one. (But if someone wants to suggest a way to handle all 3 in one query, that's a guaranteed grade A answer as far as I'm concerned.)
All of these medications show up along with the "DHPP*" ones in the SchedMeds table. If Patriot got both a DHPP3 and a Rabies shot on 6/27/2002, joining on just DogID and Date would return two records.
All of these medications show up along with the "DHPP*" ones in the SchedMeds table. If Patriot got both a DHPP3 and a Rabies shot on 6/27/2002, joining on just DogID and Date would return two records.
If you're saying that Patriot got both a DHPP3 and a Rabies shot, and you want to pick both up, then what I said will do that. When you join PuppyMaxDates back again, it will pick up all records for 6/27/2002 (assuming that's the max), and you'll end up with 2 records for Patriot.
If you're saying you ONLY want to pick up the DHPP records, then add that to the WHERE statement of PuppyMaxDates.
If you're saying you ONLY want to pick up the DHPP records, then add that to the WHERE statement of PuppyMaxDates.
ASKER
Well, I do want to pick them both up eventually, but I still need to tell them apart in the end because they have different WeeksGood and NextMed values to feed into the calculations.
But in any case, restricting the second query to DHPP records will allow the join to work as you suggested.
Thanks!
But in any case, restricting the second query to DHPP records will allow the join to work as you suggested.
Thanks!
SELECT MAX([Date_Field]), Name
FROM Your_Table
GROUP BY Name;
Try something like that will all of your other filtering criteria.