Social Icons

Rank Function

TeradataWiki-Teradata Rank FUnction
The teradata RANK function permits a column to be ranked, either based on high or low order, against other rows in the answer set. You may specify the order of ranking by use of the ORDER BY clause within the RANK function. Descending order will provide traditional ranking wherein the largest value is assigned a rank of one. Ascending order will associate the largest value with the highest rank value (i.e., typically a value > 1).

The syntax for the RANK function is:

RANK( ) OVER (ORDER BY sales DESC);

Example
Show the ranking of product sales for store 1001.

SELECT storeid
   ,prodid
   ,sales
   ,RANK( ) OVER (ORDER BY sales DESC) AS "Rank"
   FROM salestbl
   WHERE storeid = 1001;
 
Result

Storeid         prodid         sales            Rank
----------------------------------------------------------------
1001             F                 150000.00      1
1001            A                 100000.00       2
1001            C                  60000.00        3
1001            D                  35000.00        4


Things To Notice:
WHERE clause qualifies rows to be ranked.
When the order of sales is DESC, the highest sales amount is rank #1

Example
Show the lowest ranking of product sales for store 1001.

SELECT storeid
   ,prodid
   ,sales
   ,RANK( ) OVER (ORDER BY sales ASC) AS "Rank"
   FROM salestbl
   WHERE storeid = 1001;


Storeid         prodid         sales            Rank

----------------------------------------------------------------
1001             D               35000.00       1
1001             C               60000.00        2
1001             A               100000.00      3
1001             F                150000.00     4
     

Things To Notice:
When the order of sales is ASC, the lowest sales amount is rank #1
Rank #1 always appears at top of list unless overridden.

Rankings With Qualification

The QUALIFY clause allows restriction of which rankings will be output in the final result.
The QUALIFY clause allows restriction of which rankings will be output in the final result.

QUALIFY performs like the HAVING clause by requesting a specific range in the output.


Example
Get top 3 sales - any product in any store:

SELECT storeid
      ,prodid
      ,sales
       ,RANK( ) OVER (ORDER BY sales DESC)
            AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3;

Storeid         prodid         sales            Rank
----------------------------------------------------------------
1001             F                150000.00      1
1001             A                100000.00      2
1003             B                 65000.00       3
     
Things To Notice:
QUALIFY shows the ranking for the top 3 sales amounts only.

The RANK function produces an ordered sequence.  ORDER BY can override the normal sequencing.

Example
Reverse the ranking sequence in the previous example.

SELECT storeid
       ,prodid
       ,sales
       ,RANK( ) OVER (ORDER BY sales DESC) AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3
ORDER BY 4 DESC;


Storeid         prodid         sales            Rank
----------------------------------------------------------------
1003            B               65000.00            3
1001            A             100000.00            2
1001            F             150000.00            1


Things To Notice:
The ORDER BY clause in the SELECT statement may always be used to control the final order of the result set.
When the order of sales is DESC, the highest sales amount is always rank #1
After the ranking is applied, the output results are produced based on the ORDER BY sequence in the SELECT statement, if specified.

Ranking With PARTITION BY

The PARTITION BY clause may be used in conjunction with a RANK function to change the scope of the ranking. Without a PARTITION BY clause, the scope defaults to the RANK column.

Whereas the RANK( ) ORDER BY clause controls the default sort key, the PARTITION BY clause adds another level of sort to the output.


Example
Get the top three selling products in each store.

SELECT   storeid
   ,prodid
   ,sales
   ,RANK( ) OVER (PARTITION BY storeid
   ORDER BY sales DESC) AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3;


Storeid         prodid         sales            Rank

----------------------------------------------------------------
1001            F               150000.00           1
1001            A               100000.00           2
1001            C                60000.00            3
1002            A                40000.00            1
1002            C                35000.00            2
1002            D                25000.00            3
1003            B                65000.00            1
1003            D                50000.00            2
1003            A                30000.00            3 


Things To Notice:
PARTITION BY clause controls scope, i.e., rank sales within store.
Without PARTITION BY, scope would default to sales only.
QUALIFY the ranking for the top 3 sales amounts per store.
Sort sequence of output is sales descending.
Due to PARTITION BY, sort is by sales (DESC) within store (ASC).

Note -- no aggregation is done in this query.

8 comments:

  1. Ty bro it was very useful for me

    ReplyDelete
  2. thanks man understood difference between order by and partition by in rank function here

    ReplyDelete
  3. Thanks for the information. It was really helpful.

    ReplyDelete
  4. Thanks for the information. It was really helpful.

    ReplyDelete
  5. Great.. Very useful info..

    ReplyDelete
  6. I always had tough time understanding this.. Thanks for the explanation..

    ReplyDelete
  7. good explanation for interview point of view as well

    ReplyDelete