Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: GROUP BY query in Oracle
To find consecutive dates inside group ssn, solution might be somewhat
tricky, like that:
select ssn,min(date_field),max(date_field)
from
(select rownum+mod(rownum,2) grp,ssn,date_field
from
(( select a.ssn, a.date_field
from tbl a,tbl b
where a.ssn=b.ssn
and a.date_field = b.date_field +1
minus
select a.ssn, a.date_field
from tbl a,tbl b
where a.ssn=b.ssn
and a.date_field = b.date_field -1)
union
( select a.ssn, a.date_field
from tbl a,tbl b
where a.ssn=b.ssn
and a.date_field = b.date_field -1
minus
select a.ssn, a.date_field
from tbl a,tbl b
where a.ssn=b.ssn
and a.date_field = b.date_field +1) ) )
group by ssn,grp
/
I have test it, it works.
Ideas for simplier solutions?! Received on Mon Aug 16 1999 - 02:47:00 CDT