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.
