Re: Stupid GROUP BY question

2009-09-25 Thread Peter Brawley 

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

RE: Stupid GROUP BY question

2009-09-25 Thread Gavin Towey 
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.