15.00 - RANK (ANSI) - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

RANK (ANSI)

Purpose  

Returns an ordered ranking of rows based on the value_expression in the ORDER BY clause.

Type

ANSI SQL:2011 window function.

Syntax  

where:

 

Syntax element …

Specifies …

OVER

how the values, grouped according to the PARTITION BY and RESET WHEN clauses and named by value_expression in the ORDER BY clause, are ranked.

PARTITION BY

in its column_reference the column, or columns, according to which ranking resets.

PARTITION BY is optional. If there are no PARTITION BY or RESET WHEN clauses, then the entire result set, specified by the ORDER BY clause, constitutes a single group, or partition.

PARTITION BY clause is also called the window partition clause.

ORDER BY

in its value_expression the column, or columns, being ranked.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

that NULL results are to be listed first.

NULLS LAST

that NULL results are to be listed last.

RESET WHEN

the group or partition over which the function operates, depending on the evaluation of the specified condition. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition.

RESET WHEN is optional. If there are no RESET WHEN or PARTITION BY clauses, then the entire result set constitutes a single partition.

If RESET WHEN is specified, then the ORDER BY clause must be specified also.

condition

a conditional expression used to determine conditional partitioning. The condition in the RESET WHEN clause is equivalent in scope to the condition in a QUALIFY clause with the additional constraint that nested ordered analytical functions cannot specify a RESET WHEN clause. In addition, you cannot specify SELECT as a nested subquery within the condition.

The condition is applied to the rows in all designated window partitions to create sub-partitions within the particular window partitions.

For more information, see “RESET WHEN Condition Rules” on page 980 and the “QUALIFY Clause” in SQL Data Manipulation Language.

TIES LOW

that all ties get the lowest rank.

Returns an Integer data type.

TIES HIGH

that all ties get the highest rank.

Returns an Integer data type.

TIES AVG

that all ties get the average rank.

Returns a Decimal data type.

TIES DENSE

that all ties are ranked as DENSE_RANK ranks them.

Returns an Integer data type.

ANSI Compliance

This is ANSI SQL:2011 compliant.

The RESET WHEN clause is a Teradata extension to the ANSI SQL standard.

Meaning of Rank

RANK returns an ordered ranking of rows based on the value_expression in the ORDER BY clause. All rows having the same value_expression value are assigned the same rank.

If n rows have the same value_expression values, then they are assigned the same rank, call it rank r. The next distinct value receives rank r+n. And so on.

Less formally, RANK sorts a result set and identifies the numeric rank of each row in the result. RANK returns an integer that represents the rank of each row in the result.

Result Type and Attributes

For RANK() OVER (PARTITION BY x ORDER BY y direction), the data type, format, and title are as follows:

 

Data Type

Format

Title

INTEGER

the default format for the INTEGER data type

Rank(y direction)

For an explanation of the formatting characters in the format, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Example  

This example ranks salespersons by sales region based on their sales.

   SELECT sales_person, sales_region, sales_amount,
      RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC)
   FROM sales_table;
 

sales_person

sales_region

sales_amount

Rank(sales_amount)

Garabaldi

East

100

1

Baker

East

99

2

Fine

East

89

3

Adams

East

75

4

Edwards

West

100

1

Connors

West

99

2

Davis

West

99

2

The rank column in the preceding table lists salespersons in declining sales order according to the column specified in the PARTITION BY clause (sales_region) and that the rank of their sales (sales_amount) is reset when the sales_region changes.

Example  

The following SQL statement illustrates the difference between RANK(ANSI) and DENSE_RANK(ANSI), returning the RANK and DENSE_RANK for sales_person by sales_region and sales_amount.:

SELECT sales_person, sales_region, sales_amount,
 RANK() OVER
    (PARTITION BY sales_region ORDER BY sales_amount DESC) as "Rank",
 DENSE_RANK() OVER
    (PARTITION BY sales_region ORDER BY sales_amount DESC) as "DenseRank"
 FROM sales_table;
 

sales_person

sales_region

sales_amount

Rank

DenseRank

Garabaldi

East

100

1

1

Baker

East

100

1

1

Fine

East

89

3

2

Adams

East

75

4

3

Edwards

West

100

1

1

Connors

West

99

2

2

Davis

West

99

2

2

Russell

West

50

4

3