1 2 Previous Next 20 Replies Latest reply: Nov 20, 2013 9:09 AM by BluShadow RSS

    SQL Puzzler: Line Up Column-Wise Rankings

    Joe Upshaw

      We have a table that looks like the following:

       

      COL_1_RANKCOL_1_VALUECOL_2_RANKCOL_2_VALUE
      1AAA3HHH
      2BBB2GGG
      3CCC5JJJ
      4DDD1FFF
      5EEE4III

       

      We want to extract a *single* row for each case where the COL_1_RANK and COL_2_RANK are equivalent. We then want the values that correspond to this mutual rank to be displayed in this same row. So, the desired output would look like this:

       

      MUTUAL_RANKCOL_1_VALUECOL_2_VALUE
      1AAAFFF
      2BBBGGG
      3CCCHHH
      4DDDIII
      5EEEJJJ

       

      The other requirement is that we want to do this in a *single table pass*. So, we are aware of solutions using two different WITH clauses and then joining on the ranks. That's not what we are looking for here.

       

      We are suspecting that some analytic function could be used here but, just can't seem to ferret it out. Basically, for each row, take the value of COL_1_RANK and, then, over the set of all COL_2_RANK values, find the row where COL_2_RANK =s COL_1_RANK. Pull the COL_2_VALUE from this row.

       

      Any SQL Smarties out there that have a solution for doing this?

       

      Thanks,

       

       

      -Joe

        • 1. Re: SQL Puzzler: Line Up Column-Wise Rankings
          andy_schnelle

          Can you do a self join to accomplish this, i.e.:

           

          SELECT

            A.COL_1_RANK as MUTUAL_RANK,

            A.COL_1_VALUE,

            B.COL_2_VALUE

          FROM

            (SELECT COL_1_RANK, COL_1_VALUE FROM TABLE) A,

            (SELECT COL_2_RANK, COL_2_VALUE FROM TABLE) B

          WHERE A.COL_1_RANK = B.COL_2_RANK;

          • 2. Re: SQL Puzzler: Line Up Column-Wise Rankings
            Joe Upshaw

            No, Andy...that makes two passes over the table. The puzzler is how to do it in a single pass.

             

            BTW, I have just solved this and will post the answer here in a bit but, thought people might find it fun to try to figure this out before I do. As a hint, I used LISTAGG.

             

            Thanks for answering though!

             

            -Joe

            • 3. Re: SQL Puzzler: Line Up Column-Wise Rankings
              Martin Preiss

              perhaps something like this?

              drop table t;

               

              create table t (

                  col_1_rank number

                , col_1_value varchar2(20)

                , col_2_rank number

                , col_2_value varchar2(20)

              );

               

              insert into t values (1, 'AAA', 3, 'HHH');

              insert into t values (2, 'BBB', 2, 'GGG');

              insert into t values (3, 'CCC', 5, 'JJJ');

              insert into t values (4, 'DDD', 1, 'FFF');

              insert into t values (5, 'EEE', 4, 'III');

               

              with

              basedata as (

              select col_1_rank

                   , col_1_value

                   , ',' || listagg(col_2_value, ',') within group (order by col_2_rank) over() || ',' col_2_value

                from t

              )

              select col_1_rank

                   , col_1_value

                   , substr(col_2_value, instr(col_2_value, ',', 1, col_1_rank) + 1, (instr(col_2_value, ',', 1, col_1_rank + 1) - 1) - (instr(col_2_value, ',', 1, col_1_rank))) col_2_value

                from basedata

              order by col_1_rank;


              COL_1_RANK COL_1_VALUE          COL_2_VALUE

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

                       1 AAA                  FFF

                       2 BBB                  GGG

                       3 CCC                  HHH

                       4 DDD                  III

                       5 EEE                  JJJ

               

              My contribution for the obfuscation contest...

               

              But I am not sure if the WINDOW SORT operation will indeed be cheaper than a join (if this was the idea).

               

              Regards

               

              Martin

              • 4. Re: SQL Puzzler: Line Up Column-Wise Rankings
                Frank Kulash

                Hi, Joe,

                 

                You can do that using UNPIVOT and PIVOT, like this:

                 

                SELECT   *

                FROM      table_x

                UNPIVOT   (               (mutual_rank, val)

                          FOR  label IN ( (col_1_rank,  col_1_value)  AS 1

                                        , (col_2_rank,  col_2_value)  AS 2

                                        )

                          )

                PIVOT     (    MIN (val)

                          FOR  label  IN ( 1  AS col_1_value

                                         , 2  AS col_2_vlaue

                                         )

                          )

                ORDER BY  mutual_rank

                ;

                • 5. Re: SQL Puzzler: Line Up Column-Wise Rankings
                  Mark D Powell

                  Martin and Frank, and how do you know Oracle did not make two passes through the data?  Where are the explain plans showing how the query was solved?  Or is the restriction on two passes only that you cannot list the table_name in the FROM clause twice.

                   


                  • 6. Re: SQL Puzzler: Line Up Column-Wise Rankings
                    Another_user

                    SELECT col_1_rank,

                           col_1_value,

                           REGEXP_SUBSTR (listagg (col_2_value, ',') WITHIN GROUP (ORDER BY col_2_rank) OVER (),

                                          '[^,]+',

                                          1,

                                          col_1_rank)

                              col_2_value

                      FROM t

                      order by 1;

                    • 7. Re: SQL Puzzler: Line Up Column-Wise Rankings
                      Joe Upshaw

                      All,

                       

                      The restriction is based on the number of times the table is actually traversed, not the number of times it is referenced.

                       

                      I am testing out both Martin's and Frank's solutions now. I suspect Martin's is correct as it is similar to mine. Frank actually had a completely different take on it. If that one works too then, I am really glad I posted here...had not thought about pivot and unpivot.

                       

                      Martin's is better than mine. If Frank's works, I suspect that the PIVOT and UNPIVOT operations will be cheaper than either WINDOWING  or a join. So, if his works, that's the even better answer.

                       

                      If Another _User's answer works, it will be the best of all as it truly makes one, unsorted, pass through he data. I will give "Correct Answer" creds as soon as I can test these.

                       

                      BTW, here was my solution:

                       

                      WITH SORT_TARGET AS

                      ( SELECT LISTAGG(COL_1_VAL,',') WITHIN GROUP (ORDER BY COL_1_SEQUNECE) COL_1_SEQUNECE_LIST,

                               LISTAGG(COL_2_VAL,',') WITHIN GROUP (ORDER BY COL_2_SEQUNECE) COL_2_SEQUNECE_LIST

                        FROM SORT_PUZZELER ),

                      LEVEL_GENERATOR AS

                      ( SELECT LEVEL AS MUTUAL_SEQUENCE

                        FROM DUAL

                        CONNECT BY 1=1 AND

                        LEVEL < 11 )

                      SELECT LEVEL_GENERATOR.MUTUAL_SEQUENCE,

                             REGEXP_SUBSTR (COL_1_SEQUNECE_LIST, '[^,]+', 1, LEVEL_GENERATOR.MUTUAL_SEQUENCE ) AS COL_1_VAL,

                             REGEXP_SUBSTR (COL_2_SEQUNECE_LIST, '[^,]+', 1, LEVEL_GENERATOR.MUTUAL_SEQUENCE ) AS COL_2_VAL

                      FROM SORT_TARGET,LEVEL_GENERATOR;


                      Thanks, Everybody!


                      -Joe

                      • 8. Re: SQL Puzzler: Line Up Column-Wise Rankings
                        Martin Preiss

                        Mark,

                         

                        the plan for my query is:

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

                        | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

                        |   0 | SELECT STATEMENT     |      |     1 |  2027 |     6  (34)| 00:00:01 |

                        |   1 |  SORT ORDER BY       |      |     1 |  2027 |     6  (34)| 00:00:01 |

                        |   2 |   VIEW               |      |     1 |  2027 |     5  (20)| 00:00:01 |

                        |   3 |    WINDOW SORT       |      |     1 |    50 |     5  (20)| 00:00:01 |

                        |   4 |     TABLE ACCESS FULL| T    |     1 |    50 |     4   (0)| 00:00:01 |

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

                         

                        Regards

                         

                        Martin

                        • 9. Re: SQL Puzzler: Line Up Column-Wise Rankings
                          Frank Kulash

                          Hi,

                           

                          Here's the paln for the UNPIVOT that I posted:

                           

                          PLAN_TABLE_OUTPUT
                          --------------------------------------------------------------------------------
                          Plan hash value: 2997880065

                          --------------------------------------------------------------------------------
                          | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                          --------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT     |         |     8 |   200 |     7  (15)| 00:00:01 |
                          |   1 |  SORT GROUP BY PIVOT |         |     8 |   200 |     7  (15)| 00:00:01 |
                          |*  2 |   VIEW               |         |     8 |   200 |     6   (0)| 00:00:01 |
                          |   3 |    UNPIVOT           |         |       |       |            |          |
                          |   4 |     TABLE ACCESS FULL| TABLE_X |     4 |   172 |     3   (0)| 00:00:01 |
                          --------------------------------------------------------------------------------

                          Predicate Information (identified by operation id):
                          ---------------------------------------------------

                             2 - filter("unpivot_view"."MUTUAL_RANK" IS NOT NULL OR
                                        "unpivot_view"."VAL" IS NOT NULL)

                          Note
                          -----
                             - dynamic sampling used for this statement

                          Why would it do more than 1 pass for an UNPIVOT?

                          • 10. Re: SQL Puzzler: Line Up Column-Wise Rankings
                            Joe Upshaw

                            Great stuff, Another _User!.

                             

                            You win and Thanks!

                             

                            -Joe

                            • 11. Re: SQL Puzzler: Line Up Column-Wise Rankings
                              Joe Upshaw

                              It wouldn't Frank. As you say, it would only make one pass. My response was to Mark Powell who asked for clarification about the restriction. The only one offered here that would have required two passes was Andy's above.

                               

                              Yours rocks.

                               

                              -J

                               

                              Message was edited by: JoeUpshaw

                              • 12. Re: SQL Puzzler: Line Up Column-Wise Rankings
                                Peter vd Zwan

                                Hi all,

                                 

                                I thougth I had a simple solution with nth_value function like this:

                                 

                                with tab as
                                (
                                select 1 col_1_rank, 'AAA' col_1_value, 3 col_2_rank, 'HHH' col_2_value from dual union all
                                select 2, 'BBB', 2, 'GGG'  from dual union all
                                select 3, 'CCC', 5, 'JJJ' from dual union all
                                select 4, 'DDD', 1, 'FFF' from dual union all
                                select 5, 'EEE', 4, 'III' from dual
                                )
                                select
                                  col_1_rank
                                  ,col_1_value
                                  ,nth_value(col_2_value, col_1_rank) over (order by col_2_rank rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) a

                                  ,case col_1_rank
                                    when 1 then nth_value(col_2_value,1) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                    when 2 then nth_value(col_2_value,2) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                    when 3 then nth_value(col_2_value,3) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                    when 4 then nth_value(col_2_value,4) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                    when 5 then nth_value(col_2_value,5) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                  end zz

                                from
                                  tab

                                order by
                                  col_1_rank
                                ;

                                COL_1_RANK COL_1_VALUE A   ZZ

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

                                         1 AAA         III FFF

                                         2 BBB         III GGG

                                         3 CCC         III HHH

                                         4 DDD         III III

                                         5 EEE         III JJJ

                                 

                                Can any one explain why column A shows the same value on each row?

                                If I use a case and a hard coded "Nth" number it works.

                                 

                                My database:

                                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     

                                 

                                Regards,

                                 

                                Peter                                   

                                • 13. Re: SQL Puzzler: Line Up Column-Wise Rankings
                                  Joe Upshaw

                                  Here was the plan (cheapest of all)  for Another_User's:

                                   

                                  Plan

                                  SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 620  Cardinality: 10

                                    3 SORT ORDER BY  Cost: 6  Bytes: 620  Cardinality: 10

                                    2 WINDOW SORT  Cost: 6  Bytes: 620  Cardinality: 10

                                    1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

                                   

                                  Franks was second best. The WINDOW SORT operation was removed but, a VIEW operation was added

                                   

                                  Plan

                                  SELECT STATEMENT  ALL_ROWSCost: 9  Bytes: 680  Cardinality: 20 

                                    4 SORT GROUP BY PIVOT  Cost: 9  Bytes: 680  Cardinality: 20

                                    3 VIEW SYS. Cost: 8  Bytes: 680  Cardinality: 20

                                    2 UNPIVOT

                                    1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

                                   

                                  Mine was third, albeit the most (unnecessarily) complicated ...

                                   

                                  Plan

                                  SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 4,017  Cardinality: 1

                                    7 NESTED LOOPS  Cost: 6  Bytes: 4,017  Cardinality: 1

                                    3 VIEW JUPSHAW. Cost: 4  Bytes: 4,004  Cardinality: 1

                                    2 SORT GROUP BY  Bytes: 62  Cardinality: 1

                                    1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

                                    6 VIEW JUPSHAW. Cost: 2  Bytes: 13  Cardinality: 1

                                    5 CONNECT BY WITHOUT FILTERING

                                    4 FAST DUAL  Cost: 2  Cardinality: 1

                                   

                                  Martin's was close but, had an extra view operation which increased the bytes measure.

                                   

                                  Plan

                                  SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 20,330  Cardinality: 10 

                                    4 SORT ORDER BY  Cost: 6  Bytes: 20,330  Cardinality: 10

                                    3 VIEW JUPSHAW. Cost: 5  Bytes: 20,330  Cardinality: 10

                                    2 WINDOW SORT  Cost: 5  Bytes: 620  Cardinality: 10

                                    1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

                                  • 14. Re: SQL Puzzler: Line Up Column-Wise Rankings
                                    Frank Kulash

                                    Hi,

                                     

                                    For any practical application of this, it will be very difficult to notice any difference in performance.  How many rows will you ever deal with?  If you're considering any solution using LISTAGG, you must have a limit of about 1000 rows, and it will be very difficult to detect any difference in performance among any of these solutions with so few rows.

                                     

                                    By the way, cost, as returned by EXPLAIN PLAN, is not an accurate measure of anything you care about.

                                    Also, performance of single-row functions, such as REGEXP_SUBSTR, are not reflected in the EXPLAIN PLAN.

                                    1 2 Previous Next