RANK (Teradata)
Purpose
Returns the rank (1 … n) of all the rows in the group by the value of sort_expression list, with the same sort_expression values receiving the same rank.
Type
Teradata-specific function.
Syntax
where:
|
Syntax element … |
Specifies … |
|
sort_expression |
a literal or column expression or comma-separated list of literal or column expressions to be used to sort the values. USE ELEWHERE: For example, RANK(Region ASC, Store DESC), where Region ASC, Store DESC is the sort_expression list. The expression cannot contain any ordered analytical or aggregate functions. |
|
ASC |
ascending sort order. |
|
DESC |
descending sort order. The default sort direction is DESC. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Using ANSI RANK Instead of Teradata RANK
The use of Teradata RANK is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard, and is equivalent to the ANSI-compliant RANK window function. Teradata RANK is retained only for backward compatibility with existing applications.
For more information on the RANK window function, see “RANK (ANSI)” on page 1043.
Meaning of Rank
A rank r implies the existence of exactly r-1 rows with sort_expression value preceding it. All rows having the same sort_expression value are assigned the same rank.
For example, if n rows have the same sort_expression values, then they are assigned the same rank, call it rank r. The next distinct value receives rank r+n.
Less formally, RANK sorts a result set and identifies the numeric rank of each row in the result. The only argument for RANK is the sort column or columns, and the function returns an integer that represents the rank of each row in the result.
Computing Top and Bottom Values
You can use RANK to compute top and bottom values as shown in the following examples.
Top(n, column) is computed as QUALIFY RANK(column DESC) <=n.
Bottom(n, column) is computed as QUALIFY RANK(column ASC) <=n.
Result Type and Attributes
The data type, format, and title for RANK(x) are as follows:
|
Data Type |
Format |
Title |
|
INTEGER |
the default format for the INTEGER data type |
Rank(x) |
For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.
Example
Display each item, its total sales, and its sales rank for the top 100 selling items.
SELECT itemID, sumPrice, RANK(sumPrice)
FROM
(SELECT a1.itemID, SUM(a1.Price)
FROM Sales a1
GROUP BY a1.itemID AS T1(itemID, sumPrice)
QUALIFY RANK(sumPrice) <=100;
Example
Sort employees alphabetically and identify their level of seniority in the company.
SELECT EmployeeName, (HireDate - CURRENT_DATE) AS ServiceDays,
RANK(ServiceDays) AS Seniority
FROM Employee
ORDER BY EmployeeName;
The result might look like the following table:
|
EmployeeName |
Service Days |
Seniority |
|
Ferneyhough |
9931 |
2 |
|
Lucier |
9409 |
4 |
|
Revueltas |
9408 |
5 |
|
Ung |
9931 |
2 |
|
Wagner |
10248 |
1 |
Example
Sort items by category and report them in order of descending revenue rank.
SELECT Category, Item, Revenue, RANK(Revenue) AS ItemRank
FROM ItemCategory,
(SELECT Item, SUM(sales) AS Revenue
FROM DailySales
GROUP BY Item) AS ItemSales
WHERE ItemCategory.Item = ItemSales.Item
ORDER BY Category, ItemRank DESC;
The result might look like the following table.
|
Category |
Item |
Revenue |
ItemRank |
|
Hot Cereal |
Regular Oatmeal |
39112.00 |
4 |
|
Hot Cereal |
Instant Oatmeal |
44918.00 |
3 |
|
Hot Cereal |
Regular COW |
59813.00 |
2 |
|
Hot Cereal |
Instant COW |
75411.00 |
1 |