Re: deducing ranges in SQL

Try something like this :

SELECT D_NO , to_char(Min(Val)) || '-' || to_char(Max(Val)) Group By D_No

Hope it will help.

Oxnard a écrit :
> Oracle 9.2.0.6 in AIX
>
> I am trying to find ranges and where the range ends for each d_no:
> The increment of val is one
>
> My example is:
>
> select * from t1
>
> d_no val
> 1 20
> 1 21
> 1 22
> 1 23
> 1 25
> 1 1503
> 1 1504
> 3 502
> 3 503
> 3 504
> 3 600
>
> I am trying to come up with an SQL which would produce an output of
>
> d_no the_range
> 1 20-23
> 1 25
> 1 1503-1504
> 3 502-504
> 3 600
>
> I have tried using some of the analytic functions and got close with lead
> but not quite. I could do a cursor in
> PL/SQL but the table is so huge it takes way to long. In fact I did do this
> on a small table. It worked just fine.
> Also what I am showing as a table is really an in-line view of a couple of
> tables I have joined.
>
> Any ideas would really be helpful
>
> Thank you
>
>
Received on Tue Mar 21 2006 - 21:34:12 CET