deducing ranges in SQL

[Oxnard]

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

[Bertrand Guillaumin]

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
>
>

[Mark C. Stock]

Re: deducing ranges in SQL


"Oxnard" <oxnardNO_SPAM@ comcast.netwrot e in message
news:kcSdnbo_T9 IT6Z_ZRVn-rA@comcast.com. ..
: 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
:
:

what version of oracle and what have you tried?

++ mcs