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.



This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].