Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

Order by Column Values

Girish Sharma
Girish Sharma Member Posts: 4,980 Bronze Crown
edited November 2010 in SQL & PL/SQL
Hello,
There is a table having following data:

A B C D
15 20 10 18
16 17 13 20
18 23 40 29

Output Should be like:
A B C D
10 15 18 20
13 16 17 20
18 23 29 40

Means, every rows' column values should be sorted in Asc Order.

Please guide and help me.

Kind Regards
«1

Comments

  • RadhakrishnaSarma
    RadhakrishnaSarma Member Posts: 2,900
    edited January 2008
    This doesn't look like a relational data operation.

    Assume the below data before your ordering.
    Age   Salary   deptno	 street_no

    15 20 10 18
    16 17 13 20
    18 23 40 29
    Here a 15 year old fellow gets salary 10 units.


    After ordering,

    Age   Salary   deptno	 street_no

    10 15 18 20
    13 16 17 20
    18 23 29 40
    The 15 year old guy is dead and no more available.


    Cheers
    Sarma.
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Thanks for your reply. Means it is not possible to get output from select query ?

    Regards
  • 581608
    581608 Member Posts: 460
    In Oracle sort across column is not possible.

    The one way we can try is to translate the columns into Rows and then we can try. For this search this forum by the key work "pivot".

    Regards
    RK
  • 121256
    121256 Member Posts: 1,054
    Just for fun:
    exec dbms_random.seed(0)
    create table t as
      select trunc(dbms_random.value(10,40)) as a,
             trunc(dbms_random.value(10,40)) as b,
             trunc(dbms_random.value(10,40)) as c,
             trunc(dbms_random.value(10,40)) as d
        from dual connect by level <= 5
    ;
    select * from t;
    
                A             B             C             D
    ------------- ------------- ------------- -------------
               11            34            16            16
               21            12            23            38
               38            34            23            18
               24            32            34            24
               20            15            38            14
    
    select a, least(b, c, d) as b, (b + c + d) - greatest(b, c, d) - least(b, c, d) as c,  greatest(b, c, d) as d
      from
      ( select least(a, b) as a, greatest(a, b) as b, c, d from (
        select a, least(b, c) as b, greatest(b, c) as c, d from (
        select a, b, least(c, d) as c, greatest(c, d) as d from t))
      )
      order by 1, 2, 3, 4
    ;
    
                A             B             C             D
    ------------- ------------- ------------- -------------
               11            16            16            34
               12            21            23            38
               14            15            20            38
               18            23            34            38
               24            24            32            34
  • 572471
    572471 Member Posts: 984 Green Ribbon
    Just for fun:
    just for fun #2:
    SQL> select * from t;
    
             A          B          C          D
    ---------- ---------- ---------- ----------
            11         34         16         16
            21         12         23         38
            38         34         23         18
            24         32         34         24
            20         15         38         14
    
    SQL> 
    SQL> select a, least(b, c) b, greatest(b, c) c, d
      2    from (select least(a, b) a,
      3                 greatest(a, b) b,
      4                 least(c, d) c,
      5                 greatest(c, d) d
      6            from (select a, least(b, c) b, greatest(b, c) c, d
      7                    from (select least(a, b) a,
      8                                 greatest(a, b) b,
      9                                 least(c, d) c,
     10                                 greatest(c, d) d
     11                            from t)))
     12  order by 1,2,3,4
     13  /
    
             A          B          C          D
    ---------- ---------- ---------- ----------
            11         16         16         34
            12         21         23         38
            14         15         20         38
            18         23         34         38
            24         24         32         34
    
    SQL> 
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Thanks you Elic, Volder.
    It means; almost everything is possible in oracle....!
  • RadhakrishnaSarma
    RadhakrishnaSarma Member Posts: 2,900
    edited January 2008
    It means; almost everything is possible in oracle....!
    Probably yes. But bear in mind that if you have more than 4 columns in the table, the above queries will fail.

    Cheers
    Sarma
  • 572471
    572471 Member Posts: 984 Green Ribbon
    edited January 2008
    Thanks you Elic, Volder.
    It is too early to be thankful.
    We have just got warmed up :))

    Just for fun #3:
    SQL> column A format A4
    SQL> column B format A4
    SQL> column C format A4
    SQL> column D format A4
    SQL> select regexp_replace(regexp_substr(str,'<a>.*?</a>'),'<.*?>') a,
      2         regexp_replace(regexp_substr(str,'<a>.*?</a>',1,2),'<.*?>') b,
      3         regexp_replace(regexp_substr(str,'<a>.*?</a>',1,3),'<.*?>') c,
      4         regexp_replace(regexp_substr(str,'<a>.*?</a>',1,4),'<.*?>') d from (
      5  select xmlquery('for $i in row/col order by $i return <a>{$i/text()}</a>' passing
      6                  xmltype('<row><col>' || a || '</col><col>' || b ||
      7                          '</col><col>' || c || '</col><col>' || d ||
      8                          '</col></row>') returning content) .getstringval() str
      9    from t)
     10  /
    
    A    B    C    D
    ---- ---- ---- ----
    11   16   16   34
    12   21   23   38
    18   23   34   38
    24   24   32   34
    14   15   20   38
    
    SQL> 
    without regexp, remembered about this way of nodes addressing after michaels post.
    SQL> column a format A4
    SQL> column b format A4
    SQL> column c format A4
    SQL> column d format A4
    SQL> select extractvalue(str,'/a[1a,
      2         extractvalue(str,'/a[2b,
      3         extractvalue(str,'/a[3c,
      4         extractvalue(str,'/a[4d from (
      5  select xmlquery('for $i in row/col order by $i return <a>{$i/text()}</a>' passing
      6                  xmltype('<row><col>' || a || '</col><col>' || b ||
      7                          '</col><col>' || c || '</col><col>' || d ||
      8                          '</col></row>') returning content) str
      9    from t)
     10  /
    
    A    B    C    D
    ---- ---- ---- ----
    11   16   16   34
    12   21   23   38
    18   23   34   38
    24   24   32   34
    14   15   20   38
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    ERROR at line 5:
    ORA-00907: missing right parenthesis error is coming.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Similar :Fun#4:
    SQL>  with t as 
    (
     select 15 a,20 b,10 c,18 d from dual union all
     select 16,17,13,20 from dual union all
     select 18,23,40,29 from dual
    )
    --
    --
    select t.d.extract('//ROW[1]//text()').getnumberval() a,
           t.d.extract('//ROW[2]//text()').getnumberval() b,
           t.d.extract('//ROW[3]//text()').getnumberval() c,
           t.d.extract('//ROW[4]//text()').getnumberval() d 
      from (select dbms_xmlgen.getxmltype('select * from table(sys.dbms_debug_vc2coll('||a||','||b||','||c||','||d||')) order by column_value') d from t) t
    
             A          B          C          D
    ---------- ---------- ---------- ----------
            10         15         18         20
            13         16         17         20
            18         23         29         40
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    I am getting following output:

    A B C D
    ---------- ---------- ---------- ----------
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
  • 572471
    572471 Member Posts: 984 Green Ribbon
    ERROR at line 5:
    ORA-00907: missing right parenthesis error is coming.
    what is your Oracle version?
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    Windows XP
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    And what client tool are you using?
    Run the query in SQL*Plus ....
  • MaximDemenko
    MaximDemenko Member Posts: 1,613
    Both xml solutions are doing not numeric sort (for example 10000 will be pushed to the first position). To make them similar to first two solutions, one could
    order by fn:number($i) -- Volder's query
    order by to_number(column_value) -- Michaels query
    Best regards

    Maxim
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Yes, i am using SQL Plus i.e. SQL> prompt.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Well spotted ;)

    Thanks Maxim
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    try setting numwidth, i.e.
    set numwidth 10
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    set numwidth 10

    select t.d.extract('//ROW[1]//text()').getnumberval() a,
    t.d.extract('//ROW[2]//text()').getnumberval() b,
    t.d.extract('//ROW[3]//text()').getnumberval() c,
    t.d.extract('//ROW[4]//text()').getnumberval() d
    from (select dbms_xmlgen.getxmltype('select * from table(sys.dbms_debug_vc2coll('||a||','||b||','||c||','||d||')) order by to_number(column_value)') d from t) t

    A B C D
    ---------- ---------- ---------- ----------
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########

    Still output as above. (1. to_number 2. set numwidth 10)
  • 572471
    572471 Member Posts: 984 Green Ribbon
    Both xml solutions are doing not numeric sort
    order by fn:number($i) -- Volder's query
    order by to_number(column_value) -- Michaels query
    good point

    But I can't force it working ((
    Seems that it should.
    SQL> select extractvalue(str,'//col[1a,
      2         extractvalue(str,'//col[2b,
      3         extractvalue(str,'//col[3c,
      4         extractvalue(str,'//col[4d from (
      5  select xmlquery('for $i in row/col order by $i return $i' passing
      6                  xmltype('<row><col>' || a || '</col><col>' || b ||
      7                          '</col><col>' || c || '</col><col>' || d ||
      8                          '</col></row>') returning content) str
      9    from t)
     10  /
    
    A        B        C        D
    -------- -------- -------- ---------
    100      11       16       34
    12       21       23       38
    18       23       34       38
    24       24       32       34
    14       15       20       38
    
    SQL> 
    SQL> select extractvalue(str,'//col[1a,
      2         extractvalue(str,'//col[2b,
      3         extractvalue(str,'//col[3c,
      4         extractvalue(str,'//col[4d from (
      5  select xmlquery('for $i in row/col order by fn:number($i) return $i' passing
      6                  xmltype('<row><col>' || a || '</col><col>' || b ||
      7                          '</col><col>' || c || '</col><col>' || d ||
      8                          '</col></row>') returning content) str
      9    from t)
     10  /
    
    A       B        C        D
    ------- -------- -------- --------
    
    SQL> select * from v$version where rownum=1;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    
    SQL> 
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Hmmm strange ... might be a version issue.

    What happens if you simply ignore all those getnumberval()'s ?

    I.e. change the selects to
    .. t.d.extract('//ROW[1]//text()')
    ..
    or
    ...
    t.d.extract('//ROW[1]//text()').getstringval()
    ..
    @Volder:

    your query works on my 11g
    SQL>  with t as 
    (
     select 15 a,20 b,10 c,18 d from dual union all
     select 16,17,13,20 from dual union all
     select 18,23,40,29 from dual
    )
    --
    --
    select to_number(extractvalue(str,'//col[1]')) a,
           to_number(extractvalue(str,'//col[2]')) b,
           to_number(extractvalue(str,'//col[3]')) c,
           to_number(extractvalue(str,'//col[4]')) d from (
        select xmlquery('for $i in row/col order by fn:number($i) return $i' passing
                        xmltype('<row><col>' || a || '</col><col>' || b ||
                                '</col><col>' || c || '</col><col>' || d ||
                                '</col></row>') returning content) str
          from t)
    
             A          B          C          D
    ---------- ---------- ---------- ----------
            10         15         18         20
            13         16         17         20
            18         23         29         40
  • MaximDemenko
    MaximDemenko Member Posts: 1,613
    I think, this is a bug in your oracle version' xquery implementation.
    SQL> select extractvalue(str,'//col[1a,
      2          extractvalue(str,'//col[2b,
      3          extractvalue(str,'//col[3c,
      4          extractvalue(str,'//col[4d from (
      5   select xmlquery('for $i in row/col order by fn:number($i) return $i' passing
      6                   xmltype('<row><col>' || a || '</col><col>' || b ||
      7                           '</col><col>' || c || '</col><col>' || d ||
      8                           '</col></row>') returning content) str
      9     from t)
     10  ;
    
    A          B          C          D
    ---------- ---------- ---------- ----------
    16         16         34         10000
    12         21         23         38
    18         23         34         38
    24         24         32         34
    14         15         20         38
    
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
    PL/SQL Release 10.2.0.3.0 - Production
    CORE    10.2.0.3.0      Production
    TNS for Linux: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production
    Best regards

    Maxim
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    Just for fun #5:
    create table MyTable(A,B,C,D) as
    select 15,20,10,18 from dual union
    select 16,17,13,20 from dual union
    select 18,23,40,29 from dual;
    select Least(A,B,C,D) as A,
    case Least(A,B,C,D)
    when A then Least(B,C,D)
    when B then Least(A,C,D)
    when C then Least(A,B,D)
    when D then Least(A,B,C) end as B,
    case greatest(A,B,C,D)
    when A then greatest(B,C,D)
    when B then greatest(A,C,D)
    when C then greatest(A,B,D)
    when D then greatest(A,B,C) end as C,
    greatest(A,B,C,D) as D
    from MyTable;
     A   B   C   D
    -- -- -- --
    10 15 18 20
    13 16 17 20
    18 23 29 40
    my site :-)
    http://www.geocities.jp/oraclesqlpuzzle/9-45.html
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    select Least(A,B,C,D) as A,
    case Least(A,B,C,D)
    when A then Least(B,C,D)
    when B then Least(A,C,D)
    when C then Least(A,B,D)
    when D then Least(A,B,C) end as B,
    case greatest(A,B,C,D)
    when A then greatest(B,C,D)
    when B then greatest(A,C,D)
    when C then greatest(A,B,D)
    when D then greatest(A,B,C) end as C,
    greatest(A,B,C,D) as D
    from t
    /

    Returning :

    A B C D
    ---------- ---------- ---------- ----------
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
    ########## ########## ########## ##########
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    edited January 2008
    Simply, i exit from SQLPlus and re-login; so there is no issue of version; it is working in 9i as well. Now it is working:

    (A) If there are 4 columns in the table then;

    select t.d.extract('//ROW[1]//text()').getnumberval() a,
    t.d.extract('//ROW[2]//text()').getnumberval() b,
    t.d.extract('//ROW[3]//text()').getnumberval() c,
    t.d.extract('//ROW[4]//text()').getnumberval() d
    from (select dbms_xmlgen.getxmltype('select * from table(sys.dbms_debug_vc2coll('||a||','||b||','||c||','||d||')) order by to_number(column_value)') d from t) t;


    <OR>

    select Least(A,B,C,D) as A,
    case Least(A,B,C,D)
    when A then Least(B,C,D)
    when B then Least(A,C,D)
    when C then Least(A,B,D)
    when D then Least(A,B,C) end as B,
    case greatest(A,B,C,D)
    when A then greatest(B,C,D)
    when B then greatest(A,C,D)
    when C then greatest(A,B,D)
    when D then greatest(A,B,C) end as C,
    greatest(A,B,C,D) as D
    from t;

    (B) If there are 5 columns in the table then;

    select t.e.extract('//ROW[1]//text()').getnumberval() a,
    t.e.extract('//ROW[2]//text()').getnumberval() b,
    t.e.extract('//ROW[3]//text()').getnumberval() c,
    t.e.extract('//ROW[4]//text()').getnumberval() d,
    t.e.extract('//ROW[5]//text()').getnumberval() e
    from (select dbms_xmlgen.getxmltype('select * from table(sys.dbms_debug_vc2coll('||a||','||b||','||c||','||d||','||e||')) order by to_number(column_value)') e from t) t;


    <OR)

    See Below.

    Thank you very much to all of you.
    Kind Regards

    Message was edited by:
    user587535

    Message was edited by:
    user587535
This discussion has been closed.