15.00 - RANK (Teradata) - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1145-015K

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