How do I get Dense Ranking instead of Modified competition ranking?

Database
Enthusiast

How do I get Dense Ranking instead of Modified competition ranking?

For every Sale_Month, I would like to get the top 3 Item_Types for each Employee_Name dense ranked based on the on the Num_Sales. Currently I am getting "Modified competition ranking" instead of Dense Ranking"

Dense ranking ("1223" ranking)

Modified competition ranking ("1334" ranking)

How can I change my SQL to get "Dense Ranking" instead of "Modified competition ranking"?

SELECT *

From (

        SELECT

          Sale_Month

    , Employee_Name

    , Item_Type

    , Num_Sales

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

    , Rank() OVER( Partition By Sale_Month, Item_Type

                           ORDER By Num_Sales DESC               ) as myRank

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

        from myTable

)myResults

WHERE myRank < 4

ORDER BY Item_Type, Sale_Month DESC, myRank

For July Auto sales, instead of the ranking 1,2,5 I would like 1,2,3:

Sale_Month,Employee_Name,Item_Type,Num_Sales,myRank

2012-07-01,Sam White,Auto,10,1

2012-07-01,John Doe,Auto,5,2

2012-07-01,Billy Bob,Auto,5,2

2012-07-01,Jane Doe,Auto,5,2

2012-07-01,Jill Black,Auto,1,5

Here is the example table structure:

CREATE SET TABLE myTable

     (

      Sale_Month DATE FORMAT 'YYYY-MM-DD',

      Employee_Name VARCHAR(25) ,

      Item_Type VARCHAR(25),

      Num_Sales INTEGER)

PRIMARY INDEX ( Sale_Month ,Employee_Name ,Item_Type );

Here is some example data:

Sale_Month,Employee_Name,Item_Type,Num_Sales

2012-07-01,John Doe,Food,60

2012-07-01,John Doe,Clothing,10

2012-07-01,John Doe,Hardware,1

2012-07-01,John Doe,Auto,5

2012-07-01,Jane Doe,Food,75

2012-07-01,Jane Doe,Clothing,15

2012-07-01,Jane Doe,Hardware,2

2012-07-01,Jane Doe,Auto,5

2012-07-01,Billy Bob,Food,75

2012-07-01,Billy Bob,Clothing,10

2012-07-01,Billy Bob,Hardware,1

2012-07-01,Billy Bob,Auto,5

2012-07-01,Sam White,Food,30

2012-07-01,Sam White,Clothing,25

2012-07-01,Sam White,Hardware,1

2012-07-01,Sam White,Auto,10

2012-07-01,Jill Black,Food,45

2012-07-01,Jill Black,Clothing,25

2012-07-01,Jill Black,Hardware,3

2012-07-01,Jill Black,Auto,1

2012-06-01,John Doe,Food,30

2012-06-01,John Doe,Clothing,41

2012-06-01,John Doe,Hardware,8

2012-06-01,John Doe,Auto,46

2012-06-01,Jane Doe,Food,0

2012-06-01,Jane Doe,Clothing,58

2012-06-01,Jane Doe,Hardware,14

2012-06-01,Jane Doe,Auto,13

2012-06-01,Billy Bob,Food,51

2012-06-01,Billy Bob,Clothing,52

2012-06-01,Billy Bob,Hardware,47

2012-06-01,Billy Bob,Auto,13

2012-06-01,Sam White,Food,30

2012-06-01,Sam White,Clothing,33

2012-06-01,Sam White,Hardware,1

2012-06-01,Sam White,Auto,13

2012-06-01,Jill Black,Food,22

2012-06-01,Jill Black,Clothing,10

2012-06-01,Jill Black,Hardware,1

2012-06-01,Jill Black,Auto,28

4 REPLIES
Senior Apprentice

Re: How do I get Dense Ranking instead of Modified competition ranking?

Enthusiast

Re: How do I get Dense Ranking instead of Modified competition ranking?

Thanks Dieter,

For limiting the results to only the Top 3 for each Sale_Month and Item_Type, is there a more efficient way than my SQL here for Wikipedia's #3: Dense ranking "1223"?

SELECT *

FROM

(


    SELECT

          Sale_Month

        , Employee_Name

        , Item_Type

        , Num_Sales

        , SUM(CASE WHEN Num_Sales = prev_data_col THEN 0 ELSE 1 END)

          OVER (Partition By Sale_Month, Item_Type

                   ORDER By Num_Sales DESC, prev_data_col

            ROWS UNBOUNDED PRECEDING) AS "DENSE_RANK"

    From (

        SELECT

          Sale_Month

        , Employee_Name

        , Item_Type

        , Num_Sales

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

        ,MIN(Num_Sales)

         OVER( Partition By Sale_Month, Item_Type

                   ORDER By Num_Sales

                   ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING

                   ) AS prev_data_col

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

        from myTable

    )myResults

)myResults2

WHERE DENSE_RANK < 4


ORDER BY Item_Type, Sale_Month DESC, DENSE_RANK

Senior Apprentice

Re: How do I get Dense Ranking instead of Modified competition ranking?

You can remove the bold SELECT and use QUALIFY DENSE_RANK < 4.

Dieter

Enthusiast

Re: How do I get Dense Ranking instead of Modified competition ranking?

i think we can go with DISTINCT, Dervied table and Join concept..

Sel t1.col1, t1.col2, t1.R1 as sales Rank from table as t1

inner join

(Sel sales, Rank(sales) as R1 from (sel distinct sales from table)dt) as t2

on t1.sales = t2.sales