Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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",[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.
0
thwack
Asked:
thwack
  • 5
  • 4
  • 2
1 Solution
 
RiverGuyCommented:
Do you only care about the last date entry from your query?  Never 2 or more rows?  If it is just 1 row for each that you are ever after, this is a simplistic example:

SELECT MAX([Date_Field]), Name
FROM Your_Table
GROUP BY Name;


Try something like that will all of your other filtering criteria.


0
 
thwackAuthor Commented:
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.
0
 
stiemarkCommented:
I would do this as a two step process:

1st step:
Create a query PuppyMaxDates:
SELECT PuppyPgm.DogID, max(SchedMeds.DateGiven) as MaxDateGiven
FROM PuppyPgm LEFT JOIN SchedMeds ON PuppyPgm.DogID = SchedMeds.DogID
GROUP BY PuppyPgm.DogID

Now, use that guery to get the specifics on the meds:
SELECT PuppyMaxDates.DogID, PuppyMaxDates.MaxDateGiven,
DateAdd("ww",SchedMedTypes.WeeksGood,PuppyMaxDates.MaxDateGiven) AS NextDue, ...
FROM PuppyMaxDates LEFT JOIN SchedMed ON (PuppyMaxDates.DogID = SchedMed.DogID AND PuppyMaxDates.MaxDateGiven = SchedMed.DateGiven) ...
ORDER BY ...


No GROUP BY needed at that point.


Mark
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
RiverGuyCommented:
Yes, you would have to do it in 2 steps if you wanted more data displayed than you're grouping on.  
0
 
thwackAuthor Commented:
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.
0
 
stiemarkCommented:
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
0
 
thwackAuthor Commented:
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.
0
 
stiemarkCommented:
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
0
 
thwackAuthor Commented:
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.
0
 
stiemarkCommented:
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.
0
 
thwackAuthor Commented:
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!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now