Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: GROUP BY query in Oracle

Re: GROUP BY query in Oracle

From: Miodrag Prizmiæ <miodrag_at_infoprojekt.hr>
Date: Mon, 16 Aug 1999 09:47:00 +0200
Message-ID: <7p8fs7$gt$1@as102.tel.hr>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US