7 Replies Latest reply: Jul 27, 2013 10:54 PM by bubblefish RSS

    please explain ranking and partition?

    bubblefish

      I'm familiar with basic SQL and PL/SQL.  I've encountered the following clause in a query, which I don't understand:

         SELECT...

                   T1.valueA,

                    T1.dateValue,

                   T1.valueB,

                  T1.dateValue_2,

                   IsCurrent  

          FROM (SELECT
                  decode(row_number() over (
                          partition by T1.valueA order by T1.dateValue asc), 1,DECODE(sign(T1.dateValue - sysdate),1,'N','Y'), 'N') as IsCurrent
                               ,  'N' as IsCancellable
                    ,dense_rank () over ( partition by T1.valueA  order by  T1.valueB desc, T1.dateValue desc, T1.dateValue_2 desc) as rank

            FROM T1    etc...

        • 1. Re: please explain ranking and partition?
          bubblefish

          Ah, just found this very good explanation, but still open for more:

           

          http://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword

          • 2. Re: please explain ranking and partition?
            Etbin

            Your query must be very old - pre case era - or ...

            I'd rather see something (at least IMHO) more readable, maybe

             

            select ...

                   t1.valuea,

                   t1.datevalue,

                   t1.valueb,

                   t1.datevalue_2,

                   iscurrent 

              from (select case row_number() over (partition by t1.valuea order by t1.datevalue asc)

                                when 1

                                then case when t1.datevalue > sysdate

                                          then 'N'

                                          else 'Y'

                                     end

                                else 'N'

                           end iscurrent,

                           'N' iscancellable,

                           dense_rank () over (partition by t1.valuea

                                                   order by t1.valueb desc,t1.datevalue desc,t1.datevalue_2 desc

                                              ) rank

                     from t1

             

            as a non native speaker I doubt I can beat the documentation (seems pretty good to me):

            Analytic Functions DENSE_RANK

             

            Regards

             

            Etbin

            • 3. Re: please explain ranking and partition?
              michaelrozar17

              These are called as Analytic functions. You can go through the below link to know more about it.

              http://www.orafaq.com/node/55

               

              row_number() over (partition by T1.valueA order by T1.dateValue asc)

              In the above case row numbers (just like ROWNUM) are generated for each partition of t1.valueA. Consider partition just like Group By - it just groups the values in t1.valueA column and assigns row number based on the column t1.dateValue in Ascending order. Similarly RANK, DENSE_RANK etc functions work but its method of assigning row numbers differs.

              If the column's value are as below then

              function: row_number() generates:

               

              datevalue, valuea,    rownumber

              12/1/13    Jasmine        1

              13/1/13    Jasmine        2

              16/1/13    Jasmine        3

              5/2/13       Lily         1

              7/2/13       Lily         2

              9/2/13       Lily         3

              9/2/13       Lily         4

              9/2/13       Lily         5

              11/2/13      Lily         6

               

              function: dense_rank() generates:

               

              datevalue, valuea,    DenseRank

              12/1/13    Jasmine        1

              13/1/13    Jasmine        2

              16/1/13    Jasmine        3

              5/2/13       Lily         1

              7/2/13       Lily         2

              9/2/13       Lily         3

              9/2/13       Lily         3

              9/2/13       Lily         3

              11/2/13      Lily         4

              • 4. Re: please explain ranking and partition?
                bubblefish

                Thank you for the syntax cleanup.  The CASE statement is so much cleaner and easier to read.  You are correct in that this code stream originated back in the late 90's, so we are having so much joy in fixing this stuff ( LOL ).

                • 5. Re: please explain ranking and partition?
                  bubblefish

                  Thank you for your further clarification.  So it's just a way of ordering within groups.  Easier to understand with examples, than with syntax.  Thank you again.

                  • 6. Re: please explain ranking and partition?
                    rp0428

                    So it's just a way of ordering within groups.

                    No - not groups. The partition clause is about creating 'sets', not groups. The word 'group' has a very definite meaning in SQL and it is important that it not be used improperly or people will get confused.

                     

                    When data is 'grouped' the result set often has fewer rows than there were in the source data because data is aggregated.

                     

                    When data is 'partitioned' no rows are destroyed or created but 'sets' of data are produced.

                    • 7. Re: please explain ranking and partition?
                      bubblefish

                      ah, a very concise and important distinction.  thank you :-)