Re: GROUP BY query in Oracle

[Quoted] 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 - 09:47:00 CEST