List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:September 25 2009 8:35pm
Subject:RE: Stupid GROUP BY question
View as plain text  
Commonly refered to as a "groupwise max"

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
http://jan.kneschke.de/projects/mysql/groupwise-max/

Regards,
Gavin Towey

-----Original Message-----
From: Jerry Schwartz [mailto:jschwartz@strippedm]
Sent: Friday, September 25, 2009 1:28 PM
To: mysql@lists.mysql.com
Subject: Stupid GROUP BY question

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




The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Thread
Stupid GROUP BY questionJerry Schwartz25 Sep
  • Re: Stupid GROUP BY questionPeter Brawley25 Sep
  • RE: Stupid GROUP BY questionGavin Towey25 Sep