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.
