Home » SQL & PL/SQL » SQL & PL/SQL » query next value (oracle 11g)
query next value [message #658879] Thu, 29 December 2016 08:22 Go to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
Hi professionals
i have an requirement ,is there anyway i can achieve this


1
23
456
78910
1112131415
161718192021
...............
hoop next value with one increment....

1
34
678
10111213
...........
skip one number eg:2,5,9 is skipped after each hoop

Warm Regards
coorgi
Re: query next value [message #658880 is a reply to message #658879] Thu, 29 December 2016 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: query next value [message #658883 is a reply to message #658879] Thu, 29 December 2016 08:28 Go to previous messageGo to next message
John Watson
Messages: 7669
Registered: January 2010
Location: Global Village
Senior Member
Sorry, man, I have no idea what you mean.
Re: query next value [message #658884 is a reply to message #658880] Thu, 29 December 2016 08:28 Go to previous messageGo to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
Hi professionals
i have an requirement ,is there anyway i can achieve this


1
23
456
78910
1112131415
161718192021
...............
hoop next value with one increment....

1
34
678
10111213
...........
skip one number eg:2,5,9 is skipped after each hoop

Warm Regards
coorgi
Re: query next value [message #658885 is a reply to message #658884] Thu, 29 December 2016 08:50 Go to previous messageGo to next message
EdStevens
Messages: 1005
Registered: September 2013
Senior Member
Your original post was totally devoid of useful information.
Wrapping it in a code tag does not add any useful information.

You've presented a series of seemingly ramdom numbers and, it seems, you want to be able to generate the "next" number in this seemingly random series. You need to provide MUCH more detail.
Re: query next value [message #658887 is a reply to message #658879] Thu, 29 December 2016 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: query next value [message #658888 is a reply to message #658879] Thu, 29 December 2016 10:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
Registered: January 2010
Location: Connecticut, USA
Senior Member
Recursive query solution:

with r(
       val,
       n,
       l
      ) as (
             select  '1',
                     1,
                     1
               from  dual
            union all
             select  (
                      select  replace(sys_connect_by_path(n + level,','),',')
                        from  dual
                        where connect_by_isleaf = 1
                        connect by level <= l + 1
                     ),
                     n + l + 1,
                     l + 1
               from  r
               where l <= 10
           )
select  val
  from  r
  where l <= 10
  order by l
/

VAL
--------------------
1
23
456
78910
1112131415
161718192021
22232425262728
2930313233343536
373839404142434445
46474849505152535455

10 rows selected.

SQL> 

SY.
Re: query next value [message #658889 is a reply to message #658888] Thu, 29 December 2016 11:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
Registered: January 2010
Location: Connecticut, USA
Senior Member
Hierarchy + analytics:

with t as (
           select  level l,
                   sum(level - 1) over(order by level) x
             from  dual
             connect by level <= 10
          )
select  replace(sys_connect_by_path(x + level,','),',') val
  from  t
  where connect_by_isleaf = 1
  connect by prior x = x
         and prior sys_guid() is not null
         and level <= l
/

VAL
---------------------
1
23
456
78910
1112131415
161718192021
22232425262728
2930313233343536
373839404142434445
46474849505152535455

10 rows selected.

SQL> 

SY.
icon14.gif  Re: query next value [message #658891 is a reply to message #658889] Thu, 29 December 2016 11:40 Go to previous message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
Thank you so much solomon
i am fan of orafaq

Best and warm
coorgi
Previous Topic: Oracle sql id count
Next Topic: Query table names from dba_tables
Goto Forum:
  


Current Time: Mon Nov 19 01:54:40 CST 2018