Home » SQL & PL/SQL » SQL & PL/SQL » A Query to Display Sequence of Numbers in a Order (Oracle 10g, Win Xp)
A Query to Display Sequence of Numbers in a Order [message #613679] Fri, 09 May 2014 02:19 Go to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Hi,

I have Some srlno which is mentioned below

7736
7737
7738
7739
7740
7750
7761
7765
7779
7856
8047
8056
8058
8520
8521
8522
8523


This I need to print like this below;

7736 to 7740,7750,7761,7765,7779,7856,8047,8056,8058,8520 to 8523


it means whenever continuos srlno comes that should be print in (from to To format),
other wise that should be in single.

Please help me,and give solution with query.
Re: A Query to Display Sequence of Numbers in a Order [message #613690 is a reply to message #613679] Fri, 09 May 2014 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are several ways to do it, here's one:
with 
  data as (
    select srlno, 
           decode(lag(srlno,1,0) over (order by srlno),
                  srlno-1, null,
                  row_number() over (order by srlno)) flag
    from your_table
  ),
  grouped as (
    select srlno, 
           last_value(flag ignore nulls) over (order by srlno) grp
    from data
  )
select min(srlno)||decode(max(srlno),min(srlno),null,' to '||max(srlno)) range
from grouped
group by grp
order by grp
/

Of course not tested as you still refuse to post a test case.
Re: A Query to Display Sequence of Numbers in a Order [message #613692 is a reply to message #613690] Fri, 09 May 2014 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or better:
SQL> with 
  2    source as (
  3      select 7736 srlno from dual union all
  4      select 7737 from dual union all
  5      select 7738 from dual union all
  6      select 7739 from dual union all
  7      select 7740 from dual union all
  8      select 7750 from dual union all
  9      select 7761 from dual union all
 10      select 7765 from dual union all
 11      select 7779 from dual union all
 12      select 7856 from dual union all
 13      select 8047 from dual union all
 14      select 8056 from dual union all
 15      select 8058 from dual union all
 16      select 8520 from dual union all
 17      select 8521 from dual union all
 18      select 8522 from dual union all
 19      select 8523 from dual
 20    ),
 21    data as (
 22      select srlno, 
 23             decode(lag(srlno,1,0) over (order by srlno),
 24                    srlno-1, to_number(null),
 25                    row_number() over (order by srlno)) flag
 26      from source
 27    ),
 28    grouped as (
 29      select srlno, 
 30             last_value(flag ignore nulls) over (order by srlno) grp
 31      from data
 32    )
 33  select min(srlno)||decode(max(srlno),min(srlno),null,' to '||max(srlno)) range
 34  from grouped
 35  group by grp
 36  order by grp
 37  /
RANGE
------------------------------------------------------------------------------------
7736 to 7740
7750
7761
7765
7779
7856
8047
8056
8058
8520 to 8523

Re: A Query to Display Sequence of Numbers in a Order [message #613693 is a reply to message #613690] Fri, 09 May 2014 03:24 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Ok i will try this methods, any other ideas pls...
Re: A Query to Display Sequence of Numbers in a Order [message #613695 is a reply to message #613693] Fri, 09 May 2014 04:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
stalin4d wrote on Fri, 09 May 2014 13:54
any other ideas pls...


Prior to 11g there are other string aggregation techniques. If you are in 11g, you can play around with LISTAGG :

SQL> WITH DATA AS(
  2  SELECT 7736 val FROM dual UNION ALL
  3  SELECT 7737 val FROM dual UNION ALL
  4  SELECT 7738 val FROM dual UNION ALL
  5  SELECT 7739 val FROM dual UNION ALL
  6  SELECT 7740 val FROM dual UNION ALL
  7  SELECT 7750 val FROM dual UNION ALL
  8  SELECT 7761 val FROM dual UNION ALL
  9  SELECT 7765 val FROM dual UNION ALL
 10  SELECT 7779 val FROM dual UNION ALL
 11  SELECT 7856 val FROM dual UNION ALL
 12  SELECT 8047 val FROM dual UNION ALL
 13  SELECT 8056 val FROM dual UNION ALL
 14  SELECT 8058 val FROM dual UNION ALL
 15  SELECT 8520 val FROM dual UNION ALL
 16  SELECT 8521 val FROM dual UNION ALL
 17  SELECT 8522 val FROM dual UNION ALL
 18  SELECT 8523 val FROM dual)
 19  SELECT 'FROM ' || MIN_VAL || ' to ' ||
 20         SUBSTR(NUM_LIST,
 21                INSTR(NUM_LIST, ',', 1) + 1,
 22                INSTR(NUM_LIST, ',', -1, 2) - 1) || ' to ' || MAX_VAL
 23    FROM (SELECT MIN(VAL) MIN_VAL,
 24                 LISTAGG(VAL, ',') WITHIN GROUP(ORDER BY VAL) NUM_LIST,
 25                 MAX(VAL) MAX_VAL
 26            FROM DATA)
 27  /
'FROM'||MIN_VAL||'TO'||SUBSTR(
-----------------------------------------------------------------------------------------------
FROM 7736 to 7737,7738,7739,7740,7750,7761,7765,7779,7856,8047,8056,8058,8520,8521,8522 to 8523


I guess someone good with REGULAR EXPRESSIONS can make it look it more tidy.

[Updated on: Fri, 09 May 2014 04:57]

Report message to a moderator

Re: A Query to Display Sequence of Numbers in a Order [message #613696 is a reply to message #613695] Fri, 09 May 2014 04:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is, obviously, wrong.
Re: A Query to Display Sequence of Numbers in a Order [message #613697 is a reply to message #613696] Fri, 09 May 2014 05:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Fri, 09 May 2014 15:22
This is, obviously, wrong.


I completely misread the question. Please ignore my post, sorry.

P.S.: Had a heavy lunch, it made me drowsy that I misread completely. Need a coffee!
Re: A Query to Display Sequence of Numbers in a Order [message #613699 is a reply to message #613692] Fri, 09 May 2014 05:50 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Fri, 09 May 2014 04:20
Or better:


No need for for lag and last_value:

with source as (
                select 7736 srlno from dual union all
                select 7737 from dual union all
                select 7738 from dual union all
                select 7739 from dual union all
                select 7740 from dual union all
                select 7750 from dual union all
                select 7761 from dual union all
                select 7765 from dual union all
                select 7779 from dual union all
                select 7856 from dual union all
                select 8047 from dual union all
                select 8056 from dual union all
                select 8058 from dual union all
                select 8520 from dual union all
                select 8521 from dual union all
                select 8522 from dual union all
                select 8523 from dual
               ),
          t as (
                select  srlno - row_number() over(order by srlno) grp,
                        srlno
                  from  source
               )
select  case max(srlno)
          when min(srlno) then to_char(max(srlno))
          else min(srlno) || ' to ' || max(srlno)
        end range
  from  t
  group by grp
  order by grp
/

RANGE
------------------
7736 to 7740
7750
7761
7765
7779
7856
8047
8056

RANGE
------------------
8058
8520 to 8523

10 rows selected.

SQL> 


SY.
Previous Topic: Pivot concept in procedures
Next Topic: How to downgrade Oracle version in a session
Goto Forum:
  


Current Time: Thu Mar 28 10:31:33 CDT 2024