7 Replies Latest reply on Jul 28, 2013 3:54 AM by bubblefish

    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...

        • 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

          1 person found this helpful
          • 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

            1 person found this helpful
            • 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 :-)