1 2 Previous Next 27 Replies Latest reply: Apr 15, 2013 12:33 PM by Frank Kulash RSS

    Order By Columns in a Row

    user12028775
      Hi,

      I'm struggling to solve a problem. I want to sort the values of fields in each row.

      RDBMS : 11.2.0.3
      CREATE TABLE test_order
        (a NUMBER, b NUMBER ,c NUMBER ,d NUMBER
        );
      
      REM INSERTING into test_order
      Insert into "test_order" (A,B,C,D) values ('18','29','14','21');
      Insert into "test_order" (A,B,C,D) values ('40','11','29','12');
      Insert into "test_order" (A,B,C,D) values ('22','20','19','24');
      
      select * from test_order
      
               A          B          C          D
      ---------- ---------- ---------- ----------
              18         29         14         21 
              40         11         29         12 
              22         20         19         24 
      I'm trying get result in order as shown below DOES NOT matter column name.

      Each row ordered by its own columns.
               A          B          C          D
      ---------- ---------- ---------- ----------
              14         18         21         29 
              11         12         29         40 
              19         20         22         24 
      Note: my real table have more than one million records.

      Any helps is welcome.
        • 1. Re: Order By Columns in a Row
          ranit B
          Please explain the business requirement.

          While inserting, you insert data into columns - A, B, C, D ... but while sorting you are expecting to be sorted by value & not columns.
          Mostly, this's not possible.

          Let's check with other gurus.
          • 2. Re: Order By Columns in a Row
            Billy~Verreynne
            Can be done. However, a sort per row is required. A million rows selection? A million sort operations.
            SQL> create table testtab( a number, b number, c number, d number );
            
            Table created.
            
            SQL>
            SQL> insert into testtab (A,B,C,D) values ('18','29','14','21');
            
            1 row created.
            
            SQL> insert into testtab (A,B,C,D) values ('40','11','29','12');
            
            1 row created.
            
            SQL> insert into testtab (A,B,C,D) values ('22','20','19','24');
            
            1 row created.
            
            SQL>
            SQL> create or replace type TNumberSet is table of number;
              2  /
            
            Type created.
            
            SQL>
            SQL> col NUMBER_SET format a30
            SQL> select
              2          rownum,
              3          t.*,
              4          cast( multiset(
              5                          select column_value from TABLE(TNumberSet(a,b,c,d)) order by 1
              6                  )
              7                  as TNumberSet
              8          ) as NUMBER_SET
              9  from       testtab t
             10  /
            
                ROWNUM          A          B          C          D NUMBER_SET
            ---------- ---------- ---------- ---------- ---------- ------------------------------
                     1         18         29         14         21 TNUMBERSET(14, 18, 21, 29)
                     2         40         11         29         12 TNUMBERSET(11, 12, 29, 40)
                     3         22         20         19         24 TNUMBERSET(19, 20, 22, 24)
            
            SQL>
            The sorted columns are projected as a number set - not sure what you want do with the projected results. Most clients should be able to deal with the set (array/collection type) returned. If not, then the set needs to be split into individual projected columns per value in the set.

            Not sure why you would design a table and use a table in this fashion though. The design is IMO inherently problematic and unscalable.
            • 3. Re: Order By Columns in a Row
              Frank Kulash
              Hi,

              Here's one way:
              WITH     got_uk         AS
              (
                   SELECT  a, b, c, d
                   ,     ROW_NUMBER () OVER (ORDER BY  a)          AS uk
                   FROM     test_order
              )
              ,     got_r_num   AS
              (
                   SELECT       uk, abcd
                   ,       ROW_NUMBER () OVER ( PARTITION BY  uk
                                                ORDER BY          abcd
                                       )   AS r_num
                   FROM       got_uk
                   UNPIVOT       (   abcd
                            FOR label IN ( a
                                                 , b
                                           , c
                                           , d
                                           )
                             )
              )
              SELECT    a, b, c, d
              FROM       got_r_num
              PIVOT       (   MIN (abcd)
                     FOR r_num  IN  ( 1  AS a
                                         , 2  AS b
                                         , 3  AS c
                                         , 4  AS d
                              )
                     )
              ORDER BY  uk     -- if wanted
              ;
              Basically, this unpivots the data so that there's only one value per row, and then pivots it back to the original number of rows.

              We need to uniquely identify the rows, so that the values that started on the same row stay on the same row. Since the sample data you posted didn't include any such unique key, I generated one and called it uk. In your real table, you probably have a unique key already; if so, you won't need a sub-query like got_uk.

              The fact that you even want to do this suggests that the table design doesn't really suit your needs. It might be better iof the data were stored in an unpivoted for to start with.
              • 4. Re: Order By Columns in a Row
                user12028775
                Hi Billy and Frank,
                Simply fantastic.
                This need is proceedings to help in loading data in the application (IBM MAXIMO) that stores data in tables in order hierarchical (such as binary tree). Actually my numbers are like this (10,101,10120, 1012013).

                Many thanks for this help.

                I'll research about functions used here.
                • 5. Re: Order By Columns in a Row
                  Billy~Verreynne
                  Seeing as Frank is also having fun with this, and I'm bored watching a data pump import ticking over partition by partition, I thought about creating a SQL data type called TSortedNumbers - allowing you to create/store a set of numbers as a sorted list, and providing a method for returning a specific item in the set.

                  As PL/SQL code needs to be used to override the default constructor, I decided to use a PL/SQL quicksort on the number list - as oppose to an expensive context switch to SQL to have SQL doing the sort for you. No idea what (if any) performance improvements there are using a PL/SQL quicksort.

                  However, this does demonstrate the really kewl and awesome functionality Oracle provides by enabling one to define custom SQL types.
                  // TNumbers is an array/collection of numbers
                  SQL> create or replace type TNumbers as table of number;
                    2  /
                  
                  Type created.
                  
                  // package Lib, implements the well-known Quick Sort algorithm - and sorts an 
                  // array of numbers, where the array is of data type TNumbers
                  SQL> create or replace package Lib is
                    2          procedure QuickSort( array in out nocopy TNumbers );
                    3  end;
                    4  /
                  
                  Package created.
                  
                  SQL>
                  SQL> create or replace package body Lib is
                    2          procedure SwapPivots( array in out nocopy TNumbers, pivot1 number, pivot2 number ) is
                    3                  pivotValue      number;
                    4          begin
                    5                  pivotValue := array(pivot1);
                    6                  array(pivot1) := array(pivot2);
                    7                  array(pivot2) := pivotValue;
                    8          end;
                    9
                   10          procedure  Partition( array in out nocopy TNumbers, left number, right number, pivotIndex number, storeIndex out number ) is
                   11                  pivotValue      number;
                   12          begin
                   13                  pivotValue := array(pivotIndex);
                   14                  SwapPivots( array, pivotIndex, right );
                   15                  storeIndex := left;
                   16                  for i in left..right - 1 loop
                   17                          if array(i) < pivotValue then
                   18                                  SwapPivots( array, i, storeIndex );
                   19                                  storeIndex := storeIndex + 1;
                   20                          end if;
                   21                  end loop;
                   22                  SwapPivots( array, storeIndex, right );
                   23          end;
                   24
                   25          procedure QuickSort( array in out nocopy TNumbers, left number, right number ) is
                   26                  pivotIndex      number;
                   27                  pivotNewIndex   number;
                   28          begin
                   29                  if left < right then
                   30                          pivotIndex := trunc( DBMS_RANDOM.value( left, right ) );
                   31
                   32                          Partition( array, left, right, pivotIndex, pivotNewIndex );
                   33                          QuickSort( array, left, pivotNewIndex-1 );
                   34                          QuickSort( array, pivotNewIndex+1, right );
                   35                  end if;
                   36          end;
                   37
                   38          procedure QuickSort( array in out nocopy TNumbers ) is
                   39          begin
                   40                   QuickSort( array, 1, array.Count );
                   41          end;
                   42
                   43  end;
                   44  /
                  
                  Package body created.
                  
                  // We define a a custom SQL data type that contains a single property called n, 
                  // where n is of type TNumber.
                  // The default constructor for this type is: TSortedNumbers ( <array-of-numbers )
                  // We create a custom constructor with the same signature, in order to override 
                  // the default constructor.
                  SQL> create or replace type TSortedNumbers is object(
                    2          n       TNumbers,
                    3
                    4          constructor function TSortedNumbers(n TNumbers) return self as result,
                    5          member function Get(i integer) return number
                    6  );
                    7  /
                  
                  Type created.
                  
                  // Using our custom constructor, we accept the array-of-numbers parameter, 
                  // assign it to our  TSortedNumbers property n, and quick sort our property so 
                  // that the array-of-numbers is in ascending values.
                  // We also provide a method called Get(), that allows the caller to get a value 
                  // from our sorted array, by index number.
                  SQL> create or replace type body TSortedNumbers as
                    2          constructor function TSortedNumbers(n TNumbers) return self as result is
                    3          begin
                    4                  self.n := n;
                    5                  Lib.QuickSort(self.n);
                    6                  return;
                    7          end;
                    8
                    9          member function Get(i integer) return number is
                   10          begin
                   11                  return( self.n(i) );
                   12          end;
                   13
                   14  end;
                   15  /
                  
                  Type body created.
                  
                  // Create sample table and populate it with data
                  SQL> create table testtab( a number, b number, c number, d number );
                  
                  Table created.
                  
                  SQL>
                  SQL> insert into testtab (A,B,C,D) values ('18','29','14','21');
                  
                  1 row created.
                  
                  SQL> insert into testtab (A,B,C,D) values ('40','11','29','12');
                  
                  1 row created.
                  
                  SQL> insert into testtab (A,B,C,D) values ('22','20','19','24');
                  
                  1 row created.
                  
                  // To create an array of numbers, we use the TNumbers() data type. Its 
                  // constructor  is TNumbers( num1, num2, .., numn ). We pass the columns of 
                  // the rows as parameters into this constructor and it creates an array of our 
                  // column values. As we now have an array-of-numbers, we can instantiate a 
                  // TSortedNumbers object. Its constructor required an array-of-numbers 
                  // parameter. Which is what we have created using our row's columns.
                  // As the TSortedNumbers object contains a sorted array-of-numbers of our 
                  // columns, we can display the numbers in the array using the Get() method
                  // and index position in the array of the number we want to display.
                  SQL> with results as(
                    2  select
                    3          rownum,
                    4          t.*,
                    5          TSortedNumbers( TNumbers(a,b,c,d) ) as NUMBER_SET
                    6  from       testtab t
                    7  )
                    8  select
                    9          r.number_set.Get(1)     as "1",
                   10          r.number_set.Get(2)     as "2",
                   11          r.number_set.Get(3)     as "3",
                   12          r.number_set.Get(4)     as "4"
                   13  from       results r
                   14  /
                  
                           1          2          3          4
                  ---------- ---------- ---------- ----------
                          14         18         21         29
                          11         12         29         40
                          19         20         22         24
                  
                  SQL>
                  Edited by: Billy  Verreynne  on Apr 13, 2013 6:04 PM. (Added comments to example)
                  • 6. Re: Order By Columns in a Row
                    user12028775
                    Hi Billy,

                    Amazing code, who tell that it's impossible is because not met Frank and Billy. :p

                    Many thanks for your time and knowledge sharing.
                    • 7. Re: Order By Columns in a Row
                      9423755
                      I have to say, as a learner of PL/SQL, albeit a reasonably experienced user of SQL, I find these kinds of answers quite depressing because to my eye they are quite unfathomable.
                      They are very clever, but not particularly useful (to me at least) because they aren't explained in anything but the tersest of terms...they are so obtuse as to be something of a black box.
                      One wonders if one is supposed to be able to write or even understand stuff like this.
                      Meanwhile, I shall plod on through the PL/SQL language reference......
                      • 8. Re: Order By Columns in a Row
                        Hoek
                        When done with the PL/SQL language reference, you could continue here then:
                        http://docs.oracle.com/cd/E11882_01/appdev.112/e11822/adobjint.htm and get rid of that deppression ;)
                        I (sofar) never needed to resort to this kind of programming myself in real life, but it's fun to play with.
                        • 9. Re: Order By Columns in a Row
                          Billy~Verreynne
                          Jason_942375 wrote:
                          I have to say, as a learner of PL/SQL, albeit a reasonably experienced user of SQL, I find these kinds of answers quite depressing because to my eye they are quite unfathomable.
                          They are very clever, but not particularly useful (to me at least) because they aren't explained in anything but the tersest of terms...they are so obtuse as to be something of a black box.
                          I've added comments to the example, Jason. Hope it makes things a tad clearer?
                          One wonders if one is supposed to be able to write or even understand stuff like this.
                          This "stuff" is basic object orientation. Which is kind of a weird thing in the relational database SQL and stored procedure language environment.

                          However, ignore for a moment that environment and look at it as standard object orientation. The "+create type <typename> as table of <type>+" is equivalent to creating a dynamic array in C/Pascal (aka a dim in Visual Basic).

                          The "+create object <typename>+" is equivalent to the class statement in C/Pascal. A class has a constructor, properties and method. An object of a specific class is instantiated by calling that class's constructor.

                          So conceptually, these are all standard object orientation features.

                          Now in the Oracle environment, the implementation of these features uses both SQL and PL/SQL.

                          The object class and dynamic array, are SQL data types - also called SQL advance data types, or SQL custom/user data types.

                          SQL is not a programming language like C/Pascal. So this language could not be used to code the class methods and constructors of the object class. PL/SQL however is like C/Pascal. And is tightly integrated with SQL. So the methods and constructors of the object class, is done in PL/SQL.

                          The end-result is object classes and dynamic arrays that can be used in both the SQL and PL/SQL engines.
                          • 10. Re: Order By Columns in a Row
                            9423755
                            Hoek wrote:
                            When done with the PL/SQL language reference, you could continue here then:
                            http://docs.oracle.com/cd/E11882_01/appdev.112/e11822/adobjint.htm and get rid of that deppression ;)
                            I (sofar) never needed to resort to this kind of programming myself in real life, but it's fun to play with.
                            Just another document to add to the list! Thanks Hoek (I think!)
                            • 11. Re: Order By Columns in a Row
                              9423755
                              >

                              >
                              I've added comments to the example, Jason. Hope it makes things a tad clearer?
                              Hi Billy
                              Many thanks for taking the time to elaborate. I'm familiar with basic object orientation concepts so I should be able to get to grips with this. It just looks a bit strange, stranger than its equivalent in Java (although really I'm not Java programmer either!).

                              At some point I'll get to chapter 14 of McLaughlin's PL/SQL Programming book (it's sitting on my desk on my to-do list after I've finished going through the PL/SQL Language Reference, sigh), so I'll be able to revisit this at that time to see if it makes more sense.

                              Cheers,
                              Jason
                              • 12. Re: Order By Columns in a Row
                                Billy~Verreynne
                                Jason_942375 wrote:

                                It just looks a bit strange, stranger than its equivalent in Java (although really I'm not Java programmer either!).
                                I don't like Java's oo implementation much. Delphi's was a significantly better (especially property definitions that included getters and setters for a property). C# looks a lot similar - as Microsoft "stole" Borland's Delphi creator. C# is what Java should have been.

                                In Oracle SQL and PL/SQL, oo is a simplified. No private or protected properties and methods. Everything is public. Inability to call the constructor of a parent class directly from a subclass's constructor. Etc.

                                However, it brings a whole new dimension of functionality and possibilities to the Oracle db environment. Which at this stage, is largely still undiscovered by many Oracle users and customers. In part, I think, because standard features address the db requirements so well, stepping beyond that into customised SQL types and oo, is an exception.

                                Still it is fun playing around with custom types. I actually use it too some extent in standard PL/SQL programming - as some interfaces I add for my production code to use, is better suited as object classes, than the normal PL/SQL package interface.
                                • 13. Re: Order By Columns in a Row
                                  Hoek
                                  at the risk of hijacking this thread, but it's too interesting not to do
                                  Still it is fun playing around with custom types. I actually use it too some extent in standard PL/SQL programming - as some interfaces I add for my production code to use, is better suited as object classes, than the normal PL/SQL package interface.
                                  Hi Billy,

                                  Excellent explanations and demo.
                                  I too like playing around with custom types/data cartridge, but besides STRAGG (for 'older DB-versions') I yet haven't found any use for them in a production system. Could you give some (simplified, if you want) examples where you found them very useful or even better than some PL/SQL construct?
                                  • 14. Re: Order By Columns in a Row
                                    user12028775
                                    Bad comments...removed

                                    Edited by: user12028775 on 14/04/2013 12:46
                                    1 2 Previous Next