1 2 3 Previous Next 32 Replies Latest reply on Jan 29, 2008 7:06 PM by MaximDemenko

    Order by Column Values

    Girish Sharma
      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. Re: Order by Column Values
          RadhakrishnaSarma
          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.
          • 2. Re: Order by Column Values
            Girish Sharma
            Thanks for your reply. Means it is not possible to get output from select query ?

            Regards
            • 3. Re: Order by Column Values
              581608
              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
              • 4. Re: Order by Column Values
                121256
                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
                • 5. Re: Order by Column Values
                  572471
                  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> 
                  • 6. Re: Order by Column Values
                    Girish Sharma
                    Thanks you Elic, Volder.
                    It means; almost everything is possible in oracle....!
                    • 7. Re: Order by Column Values
                      RadhakrishnaSarma
                      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
                      • 8. Re: Order by Column Values
                        572471
                        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[1]') a,
                          2         extractvalue(str,'/a[2]') b,
                          3         extractvalue(str,'/a[3]') c,
                          4         extractvalue(str,'/a[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) 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
                        • 9. Re: Order by Column Values
                          Girish Sharma
                          ERROR at line 5:
                          ORA-00907: missing right parenthesis error is coming.
                          • 10. Re: Order by Column Values
                            MichaelS
                            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
                            • 11. Re: Order by Column Values
                              Girish Sharma
                              I am getting following output:

                              A B C D
                              ---------- ---------- ---------- ----------
                              ########## ########## ########## ##########
                              ########## ########## ########## ##########
                              ########## ########## ########## ##########
                              ########## ########## ########## ##########
                              ########## ########## ########## ##########
                              • 12. Re: Order by Column Values
                                572471
                                ERROR at line 5:
                                ORA-00907: missing right parenthesis error is coming.
                                what is your Oracle version?
                                • 13. Re: Order by Column Values
                                  Girish Sharma
                                  Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
                                  Windows XP
                                  • 14. Re: Order by Column Values
                                    MichaelS
                                    And what client tool are you using?
                                    Run the query in SQL*Plus ....
                                    1 2 3 Previous Next