Recently there was hold a sql quiz by presented by northern california oracle user group
https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge/comment-page-1/#comment-257
on the cheryl's birthday problem http://en.wikipedia.org/wiki/Cheryl%27s_Birthday
Unfortunatly they used dates instead of just days and months leading me to a solution the havent inteded.
Additionally the introduced a character limit of 273 to beat some days after starting the quiz.
All information and testdata could be found on the website mentioned above.
So at the end Kim Berg Hansen came up with
with s as(
select unique to_char(dateofbirth,’mm’)m
,to_char(dateofbirth,’dd’)d
from juliet.dates
)
select m
,max(d)d
from(
select max(m)m,d
from s
where m!=all(
select max(m)
from s group by d having sum(1)=1)
group by d having sum(1)=1)
group by m having sum(1)=1
and i with
with d as(
select unique to_char(dateofbirth,’MM’)m
,to_char(dateofbirth,’DD’)d from juliet.dates)
select m,min(d)d
from(
select m,d,sum(1)over(partition by d)c
from d where m!=all(
select min(m)
from d group by d having sum(1)=1))
group by m,c having sum(1)=min(c)
In fact i think as posed the year component cant be discarded therefore i had another solution
with d as(
select to_char(dateofbirth,’MM’)m
,to_char(dateofbirth,’DD’)d
from juliet.dates)
select m
,min(d)keep(dense_rank first order by c)
from(
select m,d,sum(1)over(partition by d)c
from d where m!=all(
select min(m)from d
group by d having sum(1)=1)
)group by m,c having sum(1)+c=2
The quiz is coming down now to some clueless "blank counting".
I also have some model clause approach with 370 characters length. There will be at least a separate outer select.
I wonder if there is some more neat model or recursive subquery solution or any other approach that will fit this quiz.
Personally i am done with it