Re: Here's one for the SQL experts

Any column on the SELECT must also be in the GROUP BY when using MAX() and
a few other aggregate functions.


Thanks,
Tommy Holden



From: "James H. H. Lampert" <jamesl@xxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 02/22/2013 07:14 PM
Subject: Here's one for the SQL experts
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Given this SQL (the names have been changed to protect the innocent):

SELECT CALLRECVIEW.ACCOUNT_ID, MAX(DATE_OF_CALL)
FROM ACCTVIEW, CALLRPTVIEW
WHERE ACCTVIEW.ACCOUNT_ID = CALLRECVIEW.ACCOUNT_ID
GROUP BY CALLRECVIEW.ACCOUNT_ID

we (and this was just handed to me, the first I'd heard of it) seem
unable to add any fields from ACCTVIEW to the SELECT.

I'm about to suggest changing it to "FROM ACCTVIEW JOIN CALLRECVIEW ON
ACCTVIEW.ACCOUNT_ID = CALLRECVIEW.ACCOUNT_ID," but would that make any
difference.

What are we doing wrong?

--
JHHL