Re: AW: Sql muddle

Thanks for all your kind help, I was close yet far. I mean I had the same
kind of CTE expression, but I was trying to actually show the results in
the same format as the original table, ie, like this :

client, child's date of birth

so, extracting each row that satisfied the search criteria.
Nevertheless, the solution below works great and looks so simple too.

Le 3 avril 2012 15:29, <rob@xxxxxxxxx> a écrit :

An alternative work around is to take this:

select client, min(childAge) as youngest, max(childAge) as eldest
from table group by client
having youngest< 20 and eldest> 20

and convert it to

With T1 as (
select client, min(childAge) as youngest, max(childAge) as eldest
from table group by client
)
Select client, youngest, eldest from T1
where youngest< 20 and eldest> 20

either should work - your choice.


Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 04/03/2012 09:15 AM
Subject: Re: AW: Sql muddle
Sent by: midrange-l-bounces@xxxxxxxxxxxx



On 4/3/2012 8:00 AM, Birgitta Hauser wrote:

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

Yes, of course, Birgitta. Thanks for taking the time to point that
out. Something didn't look quite right, but I couldn't put my finger on
it.

This is one of the things I wish could be fixed in SQL. I'm not sure
why it's such a problem, but the restriction really makes it difficult
to do complex grouping calculations. You basically have to type the
exact same logic twice, which provides an opportunity for typos
(something I'm VERY good at).

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