List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:September 25 2009 8:27pm
Subject:Stupid GROUP BY question
View as plain text  
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

 



Thread
Stupid GROUP BY questionJerry Schwartz25 Sep
  • Re: Stupid GROUP BY questionPeter Brawley25 Sep
  • RE: Stupid GROUP BY questionGavin Towey25 Sep