Stupid GROUP BY question

------=_NextPart_000_0574_01CA3DFD.27013050
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

It must be too late in the weekâ€=A6

=20

Suppose table Cust has one field, CustID. Suppose table Notes has four =
fields: NoteID (unique), CustID, NoteTime, and NoteText.

=20

A customer can have zero or more notes. Now hereâ€=99s the seemingly =
simple problem that Iâ€=99m trying to solve: I want to find the =
newest note (if any) for each customer.

=20

If all I want is the date, then I can do

=20

SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON =
Cust.CustID =3D Notes.Cust_ID GROUP BY Cust.CustID;

=20

That will work just fine, but now I also want the NoteText associated =
with the newest note. Obviously I canâ€=99t use MAX(NoteText). I =
could do this using a temporary table, but it seems like there should be =
another way.

=20

Regards,

=20

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

=20

www.the-infoshop.com

=20


------=_NextPart_000_0574_01CA3DFD.27013050--

Re: Stupid GROUP BY question

--------------040707040500060208040302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


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
>
>
>
> 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
>
>

--------------040707040500060208040302--

RE: Stupid GROUP BY question

Q29tbW9ubHkgcmVmZXJlZCB0byBhcyBhICJncm91cHdpc2UgbWF4Ig0KDQpo dHRwOi8vZGV2Lm15
c3FsLmNvbS9kb2MvcmVmbWFuLzUuMC9lbi9leGFtcGxlLW1heGltdW0tY29s dW1uLWdyb3VwLXJv
dy5odG1sDQpodHRwOi8vamFuLmtuZXNjaGtlLmRlL3Byb2plY3RzL215c3Fs L2dyb3Vwd2lzZS1t
YXgvDQoNClJlZ2FyZHMsDQpHYXZpbiBUb3dleQ0KDQotLS0tLU9yaWdpbmFs IE1lc3NhZ2UtLS0t
LQ0KRnJvbTogSmVycnkgU2Nod2FydHogW21haWx0bzpqc2Nod2FydHpAdGhl LWluZm9zaG9wLmNv
bV0NClNlbnQ6IEZyaWRheSwgU2VwdGVtYmVyIDI1LCAyMDA5IDE6MjggUE0N ClRvOiBteXNxbEBs
aXN0cy5teXNxbC5jb20NClN1YmplY3Q6IFN0dXBpZCBHUk9VUCBCWSBxdWVz dGlvbg0KDQpJdCBt
dXN0IGJlIHRvbyBsYXRlIGluIHRoZSB3ZWVr4oCmDQoNCg0KDQpTdXBwb3Nl IHRhYmxlIEN1c3Qg
aGFzIG9uZSBmaWVsZCwgQ3VzdElELiBTdXBwb3NlIHRhYmxlIE5vdGVzIGhh cyBmb3VyIGZpZWxk
czogTm90ZUlEICh1bmlxdWUpLCBDdXN0SUQsIE5vdGVUaW1lLCBhbmQgTm90 ZVRleHQuDQoNCg0K
DQpBIGN1c3RvbWVyIGNhbiBoYXZlIHplcm8gb3IgbW9yZSBub3Rlcy4gTm93 IGhlcmXigJlzIHRo
ZSBzZWVtaW5nbHkgc2ltcGxlIHByb2JsZW0gdGhhdCBJ4oCZbSB0cnlpbmcg dG8gc29sdmU6IEkg
d2FudCB0byBmaW5kIHRoZSBuZXdlc3Qgbm90ZSAoaWYgYW55KSBmb3IgZWFj aCBjdXN0b21lci4N
Cg0KDQoNCklmIGFsbCBJIHdhbnQgaXMgdGhlIGRhdGUsIHRoZW4gSSBjYW4g ZG8NCg0KDQoNClNF
TEVDVCBDdXN0LkN1c3RJRCwgTUFYKE5vdGVzLk5vdGVUaW1lKSBGUk9NIEN1 c3QgTEVGVCBKT0lO
IE5vdGVzIE9OIEN1c3QuQ3VzdElEID0gTm90ZXMuQ3VzdF9JRCBHUk9VUCBC WSBDdXN0LkN1c3RJ
RDsNCg0KDQoNClRoYXQgd2lsbCB3b3JrIGp1c3QgZmluZSwgYnV0IG5vdyBJ IGFsc28gd2FudCB0
aGUgTm90ZVRleHQgYXNzb2NpYXRlZCB3aXRoIHRoZSBuZXdlc3Qgbm90ZS4g T2J2aW91c2x5IEkg
Y2Fu4oCZdCB1c2UgTUFYKE5vdGVUZXh0KS4gSSBjb3VsZCBkbyB0aGlzIHVz aW5nIGEgdGVtcG9y
YXJ5IHRhYmxlLCBidXQgaXQgc2VlbXMgbGlrZSB0aGVyZSBzaG91bGQgYmUg YW5vdGhlciB3YXku
DQoNCg0KDQpSZWdhcmRzLA0KDQoNCg0KSmVycnkgU2Nod2FydHoNCg0KVGhl IEluZm9zaG9wIGJ5
IEdsb2JhbCBJbmZvcm1hdGlvbiBJbmNvcnBvcmF0ZWQNCg0KMTk1IEZhcm1p bmd0b24gQXZlLg0K
DQpGYXJtaW5ndG9uLCBDVCAwNjAzMg0KDQoNCg0KODYwLjY3NC44Nzk2IC8g RkFYOiA4NjAuNjc0
LjgzNDENCg0KDQoNCiA8aHR0cDovL3d3dy50aGUtaW5mb3Nob3AuY29tPiB3 d3cudGhlLWluZm9z
aG9wLmNvbQ0KDQoNCg0KDQpUaGUgaW5mb3JtYXRpb24gY29udGFpbmVkIGlu IHRoaXMgdHJhbnNt
aXNzaW9uIG1heSBjb250YWluIHByaXZpbGVnZWQgYW5kIGNvbmZpZGVudGlh bCBpbmZvcm1hdGlv
bi4gSXQgaXMgaW50ZW5kZWQgb25seSBmb3IgdGhlIHVzZSBvZiB0aGUgcGVy c29uKHMpIG5hbWVk
IGFib3ZlLiBJZiB5b3UgYXJlIG5vdCB0aGUgaW50ZW5kZWQgcmVjaXBpZW50 LCB5b3UgYXJlIGhl
cmVieSBub3RpZmllZCB0aGF0IGFueSByZXZpZXcsIGRpc3NlbWluYXRpb24s IGRpc3RyaWJ1dGlv
biBvciBkdXBsaWNhdGlvbiBvZiB0aGlzIGNvbW11bmljYXRpb24gaXMgc3Ry aWN0bHkgcHJvaGli
aXRlZC4gSWYgeW91IGFyZSBub3QgdGhlIGludGVuZGVkIHJlY2lwaWVudCwg cGxlYXNlIGNvbnRh
Y3QgdGhlIHNlbmRlciBieSByZXBseSBlbWFpbCBhbmQgZGVzdHJveSBhbGwg Y29waWVzIG9mIHRo
ZSBvcmlnaW5hbCBtZXNzYWdlLg0K