Re: select question

<kazelot_at_thenut.eti.pg.gda.pl> a écrit dans le message news: Pine.LNX.4.33.0107221723380.7399-100000_at_thenut.eti.pg.gda.pl...
> I have a table with varchar2(5) column, for example:
>
> > select spqmc from proba;
> spqmc
> -----
> 10000
> 10001
> 10002
> 10004
> 10005
> 10007
>
> (Unfortunatelly there is much more rows there, about 500).
>
> I need one select that would give me continuos ranges. I mean
>
> > select r_from, r_to from (some magic select);
> r_from r_to
> ---------------
> 10000 10002
> 10004 10005
> 10007 10007
>
> I came up with one, terribly slow, solution. Maybe it can be done
> in some other way.
>
> Here is what I got:
>
> 1) I found continuos block of numbers (takes about 50ms for 100 rows):
>
> SELECT A.SPQMC OD1 , B.SPQMC DO1
> FROM PROBA A, PROBA B
> WHERE
> B.SPQMC - A.SPQMC =
> (SELECT (COUNT(*)-1) FROM
> PROBA LICZ WHERE
> LICZ.SPQMC >= A.SPQMC AND SPQMC <= B.SPQMC)
> AND
> B.SPQMC >= A.SPQMC
>
> od1 do1
> ------------
> 10000 10000
> 10000 10001
> 10000 10002
> 10001 10001
> 10001 10002
> 10002 10002
> 10004 10004
> 10004 10005
> 10005 10005
> 10007 10007
>
> 2) I restrict the list "from the right" (about 1 second for 100 rows):
> SELECT OD1, MAX(DO1) DO2
> FROM (
> 1)
> )
> GROUP BY OD1
>
> od1 do2
> -----------
> 10000 10002
> 10001 10002
> 10002 10002
> 10004 10005
> 10005 10005
> 10007 10007
>
> 3) Now I restrict the list "from the left" (about 1 second for 100 rows):
> SELECT MIN(OD1) od1, DO2
> FROM (
> 2)
> )
> GROUP BY Do2
>
> od1 do2
> -----------
> 10000 10002
> 10004 10005
> 10007 10007
>
>
> I use Oracle 8.1.6. Is there any trick here that could solve or
> speed up this thing?
>

Here's a solution, don't know if it is faster than yours (hope you have an unique index on spqmc):

select tf.r_from, tt.r_to
from ( select spqmc r_from from proba t1

       where not exists (select 1 from proba t2 where t2.spqmc = t1.spqmc-1) ) tf,
     ( select spqmc r_to from proba t3
       where not exists (select 1 from proba t4 where t4.spqmc = t3.spqmc+1) ) tt
where tt.r_to-tf.r_from+1 = ( select count(*) from proba t5
                              where spqmc between tf.r_from and tt.r_to )

--
Hope this helps
Michel