Re: GROUP BY query in Oracle

From: Zile Liao <zliao_at_lbisoft.com>
Date: Mon, 16 Aug 1999 13:18:23 -0400
Message-ID: <7p9h87$ch$1_at_autumn.news.rcn.net>


Thanks :)

Miodrag PrizmiŠ wrote in message <7p8fs7$gt$1_at_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 - 19:18:23 CEST

Original text of this message