Any column on the SELECT must also be in the GROUP BY when using MAX() and
a few other aggregate functions.
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
What are we doing wrong?