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:jschwa...@the-infoshop.com] 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.
