Re: SQL Fun Challenge #2

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 4 Mar 2004 10:19:17 +0000 (UTC)
Message-ID: <c26vr5$r93$1@titan.btinternet.com>

The following is basically the same solution as Brian, Noons, and Noel have proposed, but (a) could be run by an account with only the "create session" privilege, and (b) needs only one constant input, although the suggestion of using the 20-year time lapse would 'tune' the query slightly. The other thing I've done is rewrite the approach to use some trendy 9i bits - which will be made unreadable in posting.

with age_list as (
 select rownum age
 from all_objects
 where rownum <= 36
),

product_check as (
 select

  age1.age    as youngest,
  age2.age    as middle,
  age3.age    as oldest,
  age1.age + age2.age +age3.age as summed

 from

  age_list age1,
  age_list age2,
  age_list age3

 where
  age2.age >= age1.age
 and age3.age >= age2.age
 and age1.age * age2.age * age3.age = (
   select max(age) from age_list
  )
),

summed_check as (
select
 youngest, middle, oldest, summed
from
 (
 select
  youngest, middle, oldest, summed,
  count(*) over(partition by summed) ct
 from product_check
 )
where ct > 1
)
select
 *
from summed_check
where
 oldest > middle
;

Subquery age_list generates the limited list of ages. I've chosen 36 as the limit to keep the inputs down to just one. Using some other view with lots of rows is optional.

Subquery product_check turns one column into three age columns and a sum, restricted to the specificed "product of ages" request - but I've used a subquery to match the product to the original row-count - silly, but that's why I only need one input.

Subquery summed_check implements the statement that the correct solution cannot be identified uniquely by knowing the sum of the columns.

The main query then reports any potential solutions where there actually is a single oldest child.

Of course, I've failed the test, because I didn't create, or load, a table with data; I only wrote the SQL statement.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof
  Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1078359740.735908_at_yasure...

>
> Create a table, load it with data, and write a single SQL statement to
> produce the data set required to deduce the answer ... then deduce away!
>
> -- 
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>

Received on Thu Mar 04 2004 - 04:19:17 CST