select client, min(childAge) as youngest, max(childAge) as eldest
from table group by client
having youngest< 20 and eldest> 20
This is nearly right, except correlation names or names for temporary
columns are NOT allowed within the same sub-select, i.e. youngest and eldest
cannot be used in the having clause.
Select Client, Min(ChildAge), Max(ChildAge)
From Table
Group by Client
Having Min(ChildAge) < 20 and Max(ChildAge) > 20
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Joe Pluta
Gesendet: Tuesday, 03. April 2012 14:36
An: Midrange Systems Technical Discussion
Betreff: Re: Sql muddle
On 4/3/2012 7:30 AM, Raul A. Jager W. wrote:
select client, min(childAge), max(childAge) from table where min< 20
and max> 20 ...
This is nearly right.
select client, min(childAge) as youngest, max(childAge) as eldest
from table group by client
having youngest< 20 and eldest> 20
Give the min and max meaningful names and add a group by clause. Change the
WHERE to HAVING.
I haven't tested it, but it looks correct.
Joe
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
