Home » SQL & PL/SQL » SQL & PL/SQL » why the absence of "partition by" gives diffreent results? (oracle, 10g, 10.2.0.3.0, IBM/AIX RISC System/6000)
why the absence of "partition by" gives diffreent results? [message #429294] Tue, 03 November 2009 01:34 Go to next message
kang
Messages: 89
Registered: November 2007
Member
Query 1.
with plcquery as (
  select plc_nm,
         row_number() over(order by plc_nm) rnum
    from (select 'A' plc_nm
            from dual
          union all
          select 'B' plc_nm
            from dual
          union all
          select 'C' plc_nm
            from dual
          union all
          select 'D' plc_nm
            from dual
          union all
          select 'E' plc_nm from dual)
)
select ltrim(max(sys_connect_by_path(plc_nm, ',')),',') plc_nm from plcquery 
start with rnum = 1 connect by prior rnum = rnum - 1


Query 2.
with plcquery as (
  select plc_nm,
         row_number() over(partition by 1 order by plc_nm) rnum
    from (select 'A' plc_nm
            from dual
          union all
          select 'B' plc_nm
            from dual
          union all
          select 'C' plc_nm
            from dual
          union all
          select 'D' plc_nm
            from dual
          union all
          select 'E' plc_nm from dual)
)
select ltrim(max(sys_connect_by_path(plc_nm, ',')),',') plc_nm from plcquery
start with rnum = 1 connect by prior rnum = rnum - 1


Query 1 returns A,B
Query 2 returns A,B,C,D,E

why the absence of "partition by" gives diffreent results?

I'm on

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production
Re: why the absence of "partition by" gives diffreent results? [message #429297 is a reply to message #429294] Tue, 03 November 2009 01:51 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hope you will get it from here.http://www.orafaq.com/node/55

Sriram
Re: why the absence of "partition by" gives diffreent results? [message #429298 is a reply to message #429294] Tue, 03 November 2009 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with plcquery as (
  2    select plc_nm,
  3           row_number() over(order by plc_nm) rnum
  4      from (select 'A' plc_nm
  5              from dual
  6            union all
  7            select 'B' plc_nm
  8              from dual
  9            union all
 10            select 'C' plc_nm
 11              from dual
 12            union all
 13            select 'D' plc_nm
 14              from dual
 15            union all
 16            select 'E' plc_nm from dual)
 17  )
 18  select ltrim(max(sys_connect_by_path(plc_nm, ',')),',') plc_nm from plcquery 
 19  start with rnum = 1 connect by prior rnum = rnum - 1
 20  /
PLC_NM
----------------------------------------------------------------------------------
A,B,C,D,E

1 row selected.

SQL> with plcquery as (
  2    select plc_nm,
  3           row_number() over(partition by 1 order by plc_nm) rnum
  4      from (select 'A' plc_nm
  5              from dual
  6            union all
  7            select 'B' plc_nm
  8              from dual
  9            union all
 10            select 'C' plc_nm
 11              from dual
 12            union all
 13            select 'D' plc_nm
 14              from dual
 15            union all
 16            select 'E' plc_nm from dual)
 17  )
 18  select ltrim(max(sys_connect_by_path(plc_nm, ',')),',') plc_nm from plcquery
 19  start with rnum = 1 connect by prior rnum = rnum - 1
 20  /
PLC_NM
---------------------------------------------------------------------------------------
A,B,C,D,E

1 row selected.

SQL> @v

Version Oracle : 10.2.0.4.0

Bug => upgrade.

Regards
Michel
Re: why the absence of "partition by" gives diffreent results? [message #429300 is a reply to message #429297] Tue, 03 November 2009 02:12 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Ohh i didn`t try that actually.
Thank you.
Michel.
Re: why the absence of "partition by" gives diffreent results? [message #429361 is a reply to message #429298] Tue, 03 November 2009 06:37 Go to previous message
kang
Messages: 89
Registered: November 2007
Member
Thanks.
Previous Topic: Left join problem
Next Topic: PL/SQL get manager name form employee table
Goto Forum:
  


Current Time: Sat Oct 01 11:01:59 CDT 2016

Total time taken to generate the page: 0.13937 seconds