OraFAQ Forum: SQL & PL/SQL » Order by in Listagg

Home » SQL & PL/SQL » SQL & PL/SQL » Order by in Listagg (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order by in Listagg [message #642048]

Tue, 01 September 2015 03:57

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hi All,

I have one query which returns numbers in order by ASC, but its not return properly.

Query:

select  
          listagg(v, ':') within group (order by v)      
      from  
          (select  
              regexp_substr('1:2:21:23:162:41:25:24', '[^:]+', 1, rownum) v  
          from  
              dual  
                connect by rownum <= length('1:2:21:23:162:41:25:24') - length(replace('1:2:21:23:162:41:25:24', ':')));

but now its return like:
1:162:2:21:23:25:41

Output Expected:
1:2:21:23:25:41:162

Thanks,
Xandot

Report message to a moderator