9 Replies Latest reply: Feb 18, 2014 1:38 AM by S-Max RSS

    ORDER BY NULLS LAST on multiple column

    S-Max

      Hi all,

      In my 11R2 database I have a table with many rows. After SELECT * on the table I get the following output:

                                  

      IDCOL1COL2COL3
      1111112222233333
      222223
      31111233334
      411113
      51111433335

      With "ORDER BY ... NULLS LAST" I can order the values in the first, second, or ... column and the null rows will be shown at the end of the column. I would like to get the following output:

                             

      IDCOL1COL2COL3
      1111112222233333
      2111122222333334
      31111333335
      411114

      Is it possible to get this using SQL commandos?

        • 1. Re: ORDER BY NULLS LAST on multiple column
          David Berger

          Hello S-Max

           

          What should be the logic behind your need? How data look in the table?

           

          I do not think that what you want could be a real requirement. What is the purpose of it?

          • 2. Re: ORDER BY NULLS LAST on multiple column
            Ramin Hashimzadeh

            it is not good idea ))) but you can try :

            with t(ID,

            COL1,

            COL2,

            COL3) as

            (select 1, 11111, 22222, 33333 from dual

              union all

              select 2, null, 22223, null from dual

              union all

              select 3, 11112, null, 33334 from dual

              union all

              select 4, 11113, null, null from dual

              union all

              select 5, 11114, null, 33335 from dual),

            t1 as

            (select col1, rownum r from t where col1 is not null),

            t2 as

            (select col2, rownum r from t where col2 is not null),

            t3 as

            (select col3, rownum r from t where col3 is not null)

             

            select col1, col2, col3

              from t1

             

              full outer join t2

                on t1.r = t2.r

             

              full outer join t3

                on t1.r = t3.r

             

            col1     col2       col3

            11111    22222    33333

            11112    22223    33334

            11113                 33335

            11114       

             

             

            ----

            Ramin Hashimzade

            • 3. Re: ORDER BY NULLS LAST on multiple column
              S-Max

              Hi David,

              thank you for your replay.

              Sorry, the output I need should look like:

                                      

              COL1COL2COL3
              111112222233333
              111122222333334
              11113 33335
              11114

              Without the ID column. The requirement is - the user should see the filled cells on the beginning of the column. The table contains more as 10.000 records and depending on the filter criteria the filled cells will probably be shown on end of the output.

              • 4. Re: ORDER BY NULLS LAST on multiple column
                user12222981

                you can, but its gonna have lousy performance/maintainability/...

                For example :

                 

                CREATE TABLE test_table

                (

                  col1 NUMBER,

                  col2 NUMBER,

                  col3 NUMBER

                )

                ;

                INSERT INTO test_table  (col1, col2, col3)

                VALUES  (11111, 22222, 33333);

                INSERT INTO test_table  (col1, col2, col3)

                VALUES  (NULL, 22223, NULL);

                INSERT INTO test_table  (col1, col2, col3)

                VALUES  (11112, NULL, 33334);

                INSERT INTO test_table  (col1, col2, col3)

                VALUES  (11113, NULL, NULL);

                INSERT INTO test_table  (col1, col2, col3)

                VALUES  (11114, NULL, 33335);

                 

                 

                COMMIT;

                 

                 

                SELECT q2.rn, q2.col1, q4.col2, q6.col3

                  FROM (SELECT rownum rn, q1.col1

                          FROM (SELECT col1

                                  FROM test_table

                                 ORDER BY col1 NULLS LAST) q1) q2

                      ,(SELECT rownum rn, q3.col2

                          FROM (SELECT col2

                                  FROM test_table

                                 ORDER BY col2 NULLS LAST) q3) q4

                      ,(SELECT rownum rn, q5.col3

                          FROM (SELECT col3

                                  FROM test_table

                                 ORDER BY col3 NULLS LAST) q5) q6

                WHERE q2.rn = q4.rn

                   AND q2.rn = q6.rn

                /  

                  

                DROP TABLE   test_table  ;

                 

                 

                 

                I would really recommend investigating if you can restructure the table(s).

                • 5. Re: ORDER BY NULLS LAST on multiple column
                  Partha Sarathy S

                  Not possible via ORDER BY . But you can try this.

                  WITH T1 (ID,COL1,COL2,COL3) AS (

                  SELECT 1,11111,22222,33333 FROM DUAL UNION ALL

                  SELECT 2,NULL,22223,NULL FROM DUAL UNION ALL

                  SELECT 3,11112,NULL,33334 FROM DUAL UNION ALL

                  SELECT 4,11113,NULL,NULL FROM DUAL UNION ALL

                  SELECT 5,11114,NULL,33335 FROM DUAL),

                  T2 AS(

                  SELECT ID,

                         COL1,

                         ROWNUM RN

                  FROM (

                  SELECT ID,

                         COL1

                  FROM T1

                  ORDER BY COL1 NULLS LAST)),

                  T3 AS (

                  SELECT ID,

                         COL2,

                         ROWNUM RN

                  FROM (

                  SELECT ID,

                         COL2

                  FROM T1

                  ORDER BY COL2 NULLS LAST)),

                  T4 AS (

                  SELECT ID,

                         COL3,

                         ROWNUM RN

                  FROM (

                  SELECT ID,

                         COL3

                  FROM T1

                  ORDER BY COL3 NULLS LAST))

                  SELECT COL1,

                         COL2,

                         COL3

                  FROM T2,

                       T3,

                       T4

                  WHERE T2.RN=T3.RN

                  AND T3.RN=T4.RN;

                   

                  OUTPUT:

                  11111 22222 33333

                  11112 22223 33334

                  11113       33335

                  11114 


                  • 6. Re: ORDER BY NULLS LAST on multiple column
                    odie_63

                    Using UNPIVOT/PIVOT :

                    SQL> with t (id, col1, col2, col3) as (

                      2    select 1, 11111, 22222, 33333 from dual union all

                      3    select 2, null, 22223, null   from dual union all

                      4    select 3, 11112, null, 33334  from dual union all

                      5    select 4, 11113, null, null   from dual union all

                      6    select 5, 11114, null, 33335  from dual

                      7  )

                      8  select col1, col2, col3

                      9  from (

                    10    select cid

                    11         , val

                    12         , row_number() over(partition by cid order by val nulls last) id

                    13    from t

                    14    unpivot ( val for cid in (col1, col2, col3) )

                    15  )

                    16  pivot ( min(val) for cid in ('COL1' as col1, 'COL2' as col2, 'COL3' as col3) )

                    17  order by id ;

                     

                          COL1       COL2       COL3

                    ---------- ---------- ----------

                         11111      22222      33333

                         11112      22223      33334

                         11113                 33335

                         11114           

                     

                     

                    As already said, this kind of output isn't very well suited for a relational database and traditional SQL (there's no such thing as a "cell" in a RDBMS).

                    It should be best handled in some spreadsheet tools, such as Excel, where the user can order columns individually.

                     

                    Talking about a spreadsheet-like approach, there's probably a solution involving the SQL MODEL clause as well.

                    • 7. Re: ORDER BY NULLS LAST on multiple column
                      Peter vd Zwan

                      Hi,

                       

                      See:

                       

                      SQL Puzzler: Line Up Column-Wise Rankings

                       

                      Pending on your database version you should look at the solution involving the function:

                       

                      NTH_VALUE

                       

                      Regards,

                       

                      Peter

                      • 8. Re: ORDER BY NULLS LAST on multiple column
                        Peter vd Zwan

                        Hi,

                         

                        This works on:

                        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                        PL/SQL Release 11.2.0.1.0 - Production

                        "CORE 11.2.0.1.0 Production"

                        TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

                        NLSRTL Version 11.2.0.1.0 - Production

                         

                         

                        WITH

                          t (id, col1, col2, col3) AS

                          ( SELECT 1, 11111, 22222, 33333 FROM dual

                          UNION ALL

                          SELECT 2, NULL, 22223, NULL FROM dual

                          UNION ALL

                          SELECT 3, 11112, NULL, 33334 FROM dual

                          UNION ALL

                          SELECT 4, 11113, NULL, NULL FROM dual

                          UNION ALL

                          SELECT 5, 11114, NULL, 33335 FROM dual

                          )

                        SELECT

                          nth_value(col1, ID) FROM LAST IGNORE NULLS over (order by col1 DESC rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) C1

                          ,nth_value(col2, ID) FROM LAST IGNORE NULLS over (order by col2 DESC rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) C2

                          ,nth_value(col3, ID) FROM LAST IGNORE NULLS over (order by col1 DESC rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) C3

                        FROM

                            t

                         

                        ORDER BY

                          ID

                        ;

                        With the outcome as:

                        C1    C2    C3
                        --    --    --
                        11111 22222 33333
                        11112 22223 33334
                        11113       33335
                        11114      
                                

                         

                        Regards,

                         

                        Peter

                        • 9. Re: ORDER BY NULLS LAST on multiple column
                          S-Max

                          Hi all,

                           

                          thank you very much for your tipps!

                          I have understand, such queries are performace killer.

                          @user12222981,

                          you have right. Its gonna have loosy performance/maintainability.

                           

                          On the table with 10 records it is not a problem, but for the table with more than 100.000 records are such queries the performance killer!