1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to do order by?

Discussion in 'SQL PL/SQL' started by jagadekara, Apr 1, 2015.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have a query like below.

    select rtrim (xmlagg (xmlelement (e, pt.empno || '#')).extract ('//text()'), '#') Enumbers
    from scott.emp pt
    where 1=1
    group by pt.deptno

    Enumbers
    ----------
    7782#7839#7934
    7369#7902#7876#7788#7566
    7499#7698#7654#7844#7900#7521

    Required_Output

    Enumbers
    ----------
    7782#7839#7934
    7369#7566#7788#7876#7902
    7499#7521#7654#7698#7844#7900

    I need order by in Each row. So How to do it?
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    I got it.

    select rtrim (xmlagg (xmlelement (e, pt.empno || '#') Order by pt.empno).extract ('//text()'), '#') Enumbers
    from scott.emp pt
    where 1=1
    group by pt.deptno

    Enumbers
    ----------
    7782#7839#7934
    7369#7566#7788#7876#7902
    7499#7521#7654#7698#7844#7900
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Jagadekara,

    I have 2 questions,,

    1. What is advantage of using xmlagg or Is it better to go for WM_CONCAT? If so how to use order by here/.

    SELECT rtrim(REPLACE(WM_CONCAT(PT.EMPNO||'#'),','),'#') ENUMBER FROM SCOTT.EMP PT GROUP BY PT.DEPTNO;

    2. What is the use of this condition.,
    > where 1=1
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    1) I used XMLAGG to display column values as a row. WM_CONCAT is not working in my system.

    2) where 1=1 It's nothing. Actually earlier there is a condition. So I just removed that condition and used 1=1.
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tks for the reply, Jagadekara. Isn't possible to use order by in WM_CONCAT.,?
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Don't know, because In my machine WM_CONCAT is not working. May be it's depend on Oracle Version.