Jerry,
I want to find the newest note (if any) for each customer.
See "Within-group aggregates" at
http://www.artfulsoftware.com/infotree/queries.php
<http://www.artfulsoftware.com/queries.php>
PB
-----
Jerry Schwartz wrote:
It must be too late in the week...
Suppose table Cust has one field, CustID. Suppose table Notes has four fields:
NoteID (unique), CustID, NoteTime, and NoteText.
A customer can have zero or more notes. Now here's the seemingly simple problem
that I'm trying to solve: I want to find the newest note (if any) for each
customer.
If all I want is the date, then I can do
SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON
Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID;
That will work just fine, but now I also want the NoteText associated with the
newest note. Obviously I can't use MAX(NoteText). I could do this using a
temporary table, but it seems like there should be another way.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
<http://www.the-infoshop.com> www.the-infoshop.com
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.409 / Virus Database: 270.13.112/2394 - Release Date: 09/25/09 05:51:00
