1 2 Previous Next 20 Replies Latest reply: Nov 20, 2013 9:09 AM by BluShadow Go to original post RSS
      • 15. Re: SQL Puzzler: Line Up Column-Wise Rankings
        Mark D Powell

        No, idea, but then until I check the plan I can rarely be sure what  Oracle is actually doing so I thought I would ask if the restriction was just on how the query was written or included how it was solved by Oracle.

        - -

        Until now I have not had time to look back at the problem.  Unfortunately some email just came in while I was trying to review the thread but plan to come back to see the ideas posted.  Maybe something will stick.

        - -

        Mark D Powell

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

          Hi,

           

          I know it is a bit late but here is my solution.

          Apperently the function NTH_VALUE has a bug for version 11 (in version 12 it is working fine) but you can use it 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) FROM LAST over (order by col_2_rank DESC rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) B

          from
            tab

          order by
            col_1_rank
          ;

           

          COL_1_RANK COL_1_VALUE B
          ---------- ----------- ---
                   1 AAA         FFF
                   2 BBB         GGG
                   3 CCC         HHH
                   4 DDD         III
                   5 EEE         JJJ

           

           

          I think this is the simplest (maybe not fastest) solution.

           

          Regards,

           

          Peter

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

            Frank,

             

            I agree with your observations about the explain plan. I just had several, equally correct answers, and needed a quick and dirty way to decide to whom I should give the honors. I understand that only a low level trace could provide a determinate measure. You might be able to glean some insight about single-row function based based on actual CPU and memory consumption. Maybe.

             

            The real world application of this is that we have "Top 10" measures across several dimensions. We want to produce a single row for each ranking followed by the columnar values of each dimension for that ranking. So, for example:

             

            Rank 1, Dimension 1's #1, Dimension 2's #1, etc.

            Rank 2, Dimension 1's #2, Dimension 2's #2, etc.

            Rank 3, Dimension 1's #3, Dimension 2's #3, etc.

             

            We will traverse many millions of rows to produce these measures but, within each ListAgg grouping we would only have a maximum of (number of Dimensions * 10). In any cases where the same ranking happened to occur in multiple dimensions, we would only have 1 row. So, within each group, the number is always less than or equal to 30 with 3 dimensions, i.e. very, very small.

             

            Thanks again for your contributions. They are really appreciated.

             

            -Joe

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

              Hi,

               

              Sorry, I don't understand what you want.

               

              Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
              Explain, using specific examples, how you get those results from that data.
              See the forum FAQ: https://forums.oracle.com/message/9362002

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

                Frank,

                 

                Did you maybe reply to the wrong thread?

                 

                -Joe

                • 20. Re: SQL Puzzler: Line Up Column-Wise Rankings
                  BluShadow

                  PetervdZwan wrote:

                   

                  Hi,

                   

                  I know it is a bit late but here is my solution.

                  Apperently the function NTH_VALUE has a bug for version 11 (in version 12 it is working fine) but you can use it 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) FROM LAST over (order by col_2_rank DESC rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) B

                  from
                    tab

                  order by
                    col_1_rank
                  ;

                   

                  COL_1_RANK COL_1_VALUE B
                  ---------- ----------- ---
                           1 AAA         FFF
                           2 BBB         GGG
                           3 CCC         HHH
                           4 DDD         III
                           5 EEE         JJJ

                   

                   

                  I think this is the simplest (maybe not fastest) solution.

                   

                  Regards,

                   

                  Peter

                   

                  Good answer Peter.

                   

                  In terms of the explain plan (for what it's worth) it's pretty simplistic...

                   

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

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

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

                  |   0 | SELECT STATEMENT    |      |     5 |   180 |     5  (40)| 00:00:01 |

                  |   1 |  SORT ORDER BY      |      |     5 |   180 |     5  (40)| 00:00:01 |

                  |   2 |   WINDOW SORT       |      |     5 |   180 |     5  (40)| 00:00:01 |

                  |   3 |    TABLE ACCESS FULL| TAB  |     5 |   180 |     3   (0)| 00:00:01 |

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

                   

                  and it's not limited by the size of strings as in the LISTAGG type answers.

                  Can't get much better, IMHO, than using a single analytical function that's designed (although buggy) to do exactly what the requirement is asking for.

                  1 2 Previous Next