Home » SQL & PL/SQL » SQL & PL/SQL » Selecting Ranges
Selecting Ranges [message #248214] Thu, 28 June 2007 05:25 Go to next message
arunprasad_bh
Messages: 32
Registered: June 2007
Member
Hi,

I have a number column in a table. Values are linear with some missing values
e.g. 1, 2,3, 6, 7, 8, 10, 12, 13, 45 etc

I need a query to select the ranges from above data.
e.g. 1-3, 6-8, 10-, 12-13, 45- etc.

Can you suggest how this can be achieved in SQL(not using PL/SQL) query.

Thanks
Aruna
Re: Selecting Ranges [message #248235 is a reply to message #248214] Thu, 28 June 2007 06:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think you might find this thread interesting. Yes, the search button still works. Wink

MHE
Re: Selecting Ranges [message #248236 is a reply to message #248214] Thu, 28 June 2007 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select val, 
  4             case when lag(val) over (order by val) != val-1
  5               then val
  6             end grp
  7      from t
  8    ),
  9    grouping as (
 10      select val, max(grp) over (order by val) grp
 11      from data
 12    )
 13  select min(val)||'-'||decode(max(val),min(val),to_number(null),max(val)) val
 14  from grouping
 15  group by grp
 16  order by grp nulls first
 17  /
VAL
------------------------------
1-3
6-8
10-
12-13
45-

5 rows selected.

Regards
Michel
Re: Selecting Ranges [message #248265 is a reply to message #248236] Thu, 28 June 2007 08:49 Go to previous message
arunprasad_bh
Messages: 32
Registered: June 2007
Member
Maaher, Michel

Excellent. Appreciate your help.

Thanks,
Aruna
Previous Topic: Distinct - Why NULL values are treated as single value ?
Next Topic: Unable to Exit from the Program in the middle (Merged)
Goto Forum:
  


Current Time: Sat Dec 03 21:58:23 CST 2016

Total time taken to generate the page: 0.07849 seconds