Home » SQL & PL/SQL » SQL & PL/SQL » SQL/PLSQL
SQL/PLSQL [message #258620] Mon, 13 August 2007 03:45 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi,

I need the start number and end number of the series, as per given input. Where the sequence number is break that is end number.

INPUT
Serial_number
1
2
3
4
5
10
11
12
50
51
52
55
56
57
100

OUTPUT
Start_number End_Number
1 5
10 12
50 52
55 57
100

Arvind
Re: SQL/PLSQL [message #258628 is a reply to message #258620] Mon, 13 August 2007 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select val, 
  4             case when nvl(lag(val) over(order by val),val)!=val-1
  5                  then val
  6             end grp
  7      from t
  8    )
  9  select min(val) start_number, max(val) end_number
 10  from (select val, max(grp) over(order by val) grp from data)
 11  group by grp
 12  order by grp
 13  /
START_NUMBER END_NUMBER
------------ ----------
           1          5
          10         12
          50         52
          55         57
         100        100

5 rows selected.

Regards
Michel
Re: SQL/PLSQL [message #258651 is a reply to message #258628] Mon, 13 August 2007 04:39 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Thanks working fine
Re: SQL/PLSQL [message #259858 is a reply to message #258651] Thu, 16 August 2007 12:59 Go to previous message
jrich
Messages: 35
Registered: February 2006
Member
Another solution...

select min(val) seq_start, max(val) seq_end from
  (select val from test1 order by val)
group by val-rownum
order by seq_start;
Previous Topic: ORA-01002: fetch out of sequence
Next Topic: SQL query help (merged 2 cross-posts)
Goto Forum:
  


Current Time: Thu Apr 25 04:53:32 CDT 2024