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

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