All Forums Database
teradatatester 69 posts Joined 01/10
30 Jul 2012
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
 

dnoeth 4628 posts Joined 11/04
30 Jul 2012

Check http://developer.teradata.com/node/8048

Dieter

Dieter

teradatatester 69 posts Joined 01/10
02 Aug 2012

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

dnoeth 4628 posts Joined 11/04
03 Aug 2012

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

Dieter

Dieter

mohan.mscss 31 posts Joined 04/11
18 Sep 2012

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
 
 
 

You must sign in to leave a comment.