2 Replies Latest reply: Jul 19, 2013 4:37 AM by user555994 RSS

    Rank of computed column is not wokring

    user555994

      Hello PL/SQL Gurus/experts,


      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
      I have following table -

       

      drop table t3;
      create table t3(Pat_NM,Hospital,Test_Range,Total,LyTotal) as select
      'Andy','Batra','> 10 Mph','20000','20000' from dual union all select
      'Andy','Fortis','1-3 Mph','24500','20000' from dual union all select
      'Andy','Max','5-10 Mph','10600','20000' from dual union all select
      'Andy','Max','5-10 Mph','22500','20000' from dual union all select
      'Andy','Aashiana','5-10 Mph','110600','20000' from dual union all select
      'Andy','Amar','5-10 Mph','34800','20000' from dual union all select
      'Andy','Max','5-10 Mph','600','20000' from dual union all select
      'Andy','Columbia','< 1 Mph','27700','20000' from dual union all select
      'Andy','Nimhans','< 1 Mph','50000','20000' from dual union all select
      'Andy','Meenam','< 1 Mph','11000','20000' from dual union all select
      'Andy','Meeran','5-10 Mph','24625','20000' from dual union all select
      'Andy','Mnagamani','> 10 Mph','12000','20000' from dual union all select
      'Andy','Murari','> 10 Mph','20600','20000' from dual union all select
      'Andy','Triveni','5-10 Mph','16500','20000' from dual union all select
      'Cindy','Batra','5-10 Mph','14700','20000' from dual union all select
      'Cindy','Max','< 1 Mph','170000','20000' from dual union all select
      'Cindy','Apollo Medical Centre','> 10 Mph','19000','20000' from dual union all select
      'Cindy','MLal','1-3 Mph','22600','20000' from dual union all select    
      'Cindy','Columbia','< 1 Mph','28900','20000' from dual union all select
      'Cindy','Asian','1-3 Mph','27900','20000' from dual union all select
      'Cindy','Mahagun','< 1 Mph','28700','20000' from dual union all select
      'Cindy','Manipal','< 1 Mph','29040','20000' from dual union all select
      'Cindy','Prestige','< 1 Mph','12700','20000' from dual union all select
      'Cindy','A.G.M.','< 1 Mph','97800','20000' from dual union all select
      'Cindy','Shobha','< 1 Mph','700','20000' from dual union all select
      'Cindy','Aashiana','5-10 Mph','23450','20000' from dual union all select
      'Cindy','Amar','1-3 Mph','21325','20000' from dual union all select
      'Cindy','Childs Trust','5-10 Mph','22775','20000' from dual union all select
      'Cindy','Crescent ','< 1 Mph','20025','20000' from dual;
      

       

      I have a computation of Anlzd Rank as that folloing logic -
      Rank ((Cy Yr Tot *Factor - Prev Yr Tot)/Prev Yr Tot)

      and it is not coming correct -

       

      Currently i am using the following DML -

       

      select Pat_NM,
      decode(grouping(Test_Range), 0, Test_Range, 'Total') "Test Range",
      Hospital,
      SUM (Total) "Total",
      SUM (AnlzdTotal) "Anlzd Total",
      SUM (LyTotal) "Last Yr Total",
      decode(grouping(Test_Range), 0, max(rank), null) Rank,
      decode(grouping(Test_Range), 0, max(lyrank), null) "Anlzd Rank"
      from
      (Select Pat_NM,
               Hospital,
               Test_Range,
               SUM (Total) Total,
        SUM (AnlzdTot) AnlzdTotal,
        SUM (LyTotal) LyTotal,
               DENSE_RANK ()
                  OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (LyTotal) DESC)
                  AS LYRANK,
       DENSE_RANK ()
                  OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (Total) DESC)
                  AS RANK,
               ROW_NUMBER ()
                  OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (Total) DESC)
                  AS rk
      from
      (
      SELECT Pat_NM,
               Hospital,
               Test_Range,
               SUM (Total) Total,
        SUM (LyTotal) LyTotal,
        sum (Total) *1.85 AnlzdTot,
        (sum (Total * 1.85 - LyTotal)/nullif(sum(LyTotal),0)) AnlzdTotRank         
          FROM t3
      GROUP BY Pat_NM, Hospital, Test_Range
      )
      group by Pat_NM, Hospital, Test_Range)
      group by grouping sets((Pat_NM, Test_Range,Hospital),())
      order by Pat_NM,Test_Range, Rank;
      
      

       

      Output data i am getting -

       

      PAT_N Test Ran HOSPITAL                   Total Anlzd Total Last Yr Total       RANK Anlzd Rank
      ----- -------- --------------------- ---------- ----------- ------------- ---------- ----------
      Andy  1-3 Mph  Fortis                     24500       45325         20000          1          1
      Andy  5-10 Mph Aashiana                  110600      204610         20000          1          2
      Andy  5-10 Mph Amar                       34800       64380         20000          2          2
      Andy  5-10 Mph Max                        33700       62345         60000          3          1
      Andy  5-10 Mph Meeran                     24625    45556.25         20000          4          2
      Andy  5-10 Mph Triveni                    16500       30525         20000          5          2
      Andy  < 1 Mph  Nimhans                    50000       92500         20000          1          1
      Andy  < 1 Mph  Columbia                   27700       51245         20000          2          1
      Andy  < 1 Mph  Meenam                     11000       20350         20000          3          1
      Andy  > 10 Mph Murari                     20600       38110         20000          1          1
      Andy  > 10 Mph Batra                      20000       37000         20000          2          1
      PAT_N Test Ran HOSPITAL                   Total Anlzd Total Last Yr Total       RANK Anlzd Rank
      ----- -------- --------------------- ---------- ----------- ------------- ---------- ----------
      Andy  > 10 Mph Mnagamani                  12000       22200         20000          3          1
      Cindy 1-3 Mph  Asian                      27900       51615         20000          1          1
      Cindy 1-3 Mph  MLal                       22600       41810         20000          2          1
      Cindy 1-3 Mph  Amar                       21325    39451.25         20000          3          1
      Cindy 5-10 Mph Aashiana                   23450     43382.5         20000          1          1
      Cindy 5-10 Mph Childs Trust               22775    42133.75         20000          2          1
      Cindy 5-10 Mph Batra                      14700       27195         20000          3          1
      Cindy < 1 Mph  Max                       170000      314500         20000          1          1
      Cindy < 1 Mph  A.G.M.                     97800      180930         20000          2          1
      Cindy < 1 Mph  Manipal                    29040       53724         20000          3          1
      Cindy < 1 Mph  Columbia                   28900       53465         20000          4          1
      PAT_N Test Ran HOSPITAL                   Total Anlzd Total Last Yr Total       RANK Anlzd Rank
      ----- -------- --------------------- ---------- ----------- ------------- ---------- ----------
      Cindy < 1 Mph  Mahagun                    28700       53095         20000          5          1
      Cindy < 1 Mph  Crescent                   20025    37046.25         20000          6          1
      Cindy < 1 Mph  Prestige                   12700       23495         20000          7          1
      Cindy < 1 Mph  Shobha                       700        1295         20000          8          1
      Cindy > 10 Mph Apollo Medical Centre      19000       35150         20000          1          1
            Total                              925640     1712434        580000
      

       

      Data i want -

       

      PAT_N Test Ran HOSPITAL                   Total Anlzd Total Last Yr Total       RANK Anlzd Rank
      ----- -------- --------------------- ---------- ----------- ------------- ---------- ----------
      Andy  1-3 Mph  Fortis                     24500       45325         20000          1          1
      Andy  5-10 Mph Aashiana                  110600      204610         20000          1          1
      Andy  5-10 Mph Amar                       34800       64380         20000          2          2
      Andy  5-10 Mph Max                        33700       62345         60000          3          5
      Andy  5-10 Mph Meeran                     24625    45556.25         20000          4          3
      Andy  5-10 Mph Triveni                    16500       30525         20000          5          4
      Andy  < 1 Mph  Nimhans                    50000       92500         20000          1          1
      Andy  < 1 Mph  Columbia                   27700       51245         20000          2          2
      Andy  < 1 Mph  Meenam                     11000       20350         20000          3          3
      Andy  > 10 Mph Murari                     20600       38110         20000          1          1
      Andy  > 10 Mph Batra                      20000       37000         20000          2          2
      PAT_N Test Ran HOSPITAL                   Total Anlzd Total Last Yr Total       RANK Anlzd Rank
      ----- -------- --------------------- ---------- ----------- ------------- ---------- ----------
      Andy  > 10 Mph Mnagamani                  12000       22200         20000          3          3
      Cindy 1-3 Mph  Asian                      27900       51615         20000          1          1
      Cindy 1-3 Mph  MLal                       22600       41810         20000          2          2
      Cindy 1-3 Mph  Amar                       21325    39451.25         20000          3          3
      Cindy 5-10 Mph Aashiana                   23450     43382.5         20000          1          1
      Cindy 5-10 Mph Childs Trust               22775    42133.75         20000          2          2
      Cindy 5-10 Mph Batra                      14700       27195         20000          3          3
      Cindy < 1 Mph  Max                       170000      314500         20000          1          1
      Cindy < 1 Mph  A.G.M.                     97800      180930         20000          2          2
      Cindy < 1 Mph  Manipal                    29040       53724         20000          3          3
      Cindy < 1 Mph  Columbia                   28900       53465         20000          4          4
      PAT_N Test Ran HOSPITAL                   Total Anlzd Total Last Yr Total       RANK Anlzd Rank
      ----- -------- --------------------- ---------- ----------- ------------- ---------- ----------
      Cindy < 1 Mph  Mahagun                    28700       53095         20000          5          5
      Cindy < 1 Mph  Crescent                   20025    37046.25         20000          6          6
      Cindy < 1 Mph  Prestige                   12700       23495         20000          7          7
      Cindy < 1 Mph  Shobha                       700        1295         20000          8          8
      Cindy > 10 Mph Apollo Medical Centre      19000       35150         20000          1          9
            Total                              925640     1712434        580000
      

       

      Thanks in advance for all of your effort , time and valuable feedback.

        • 1. Re: Rank of computed column is not wokring
          Frank Kulash

          Hi,

           

          Whenever you have a question, explain how you get the desired results from the given data.

          It looks like you want to compute 2 new columns, that are both like the results of ROW_NUMBER (that is, no duplicates, and no numbers skipped), except for row 33 of your results:

           

           

          Cindy > 10 Mph Apollo Medical Centre      19000       35150         20000          1          9 

           

          Since this is the only row with 'Cindy' and '> 10 Mph', I don't see why both of the computed columns aren't 1.

          Are you using DENSE_RANK instead of ROW_NUMBER because of how you want to handle ties (that is, 2 or more rows with the same pat_nm, test_range, hospital and total)?  If so, wouldn't it be a good idea to have some ties in the sample data?

           

          RANK is not a good column name.  There's a built-in function with the same name, and using it as a column name will cause confusion if not errors.  Use something like rnk or unalalyzed_rank  instead.

           

          That column seems to be assigned in descending order of total; is that right?  How is the last column, "Anlzd Rank", computed?

           


          • 2. Re: Rank of computed column is not wokring
            user555994

            Thanks for the feedback FrankKulash, i checked that it was due to the 'nulls last' to be added in following rows -

             

            DENSE_RANK ()

                        OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (LyTotal) DESC nulls last)

                        AS LYRANK,

            DENSE_RANK ()

                        OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (Total) DESC nulls last)

                        AS RANK,

            DENSE_RANK ()

                        OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (AnlzdTotRank) DESC nulls last)

                        AS ANLZDRANK,

                     ROW_NUMBER ()

                        OVER (PARTITION BY test_range || pat_nm ORDER BY SUM (Total) DESC)

                        AS rk