ROW_NUMBER

Teradata Database SQL Functions, Operators, Expressions, and Predicates

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

ROW_NUMBER

Purpose  

Returns the sequential row number, where the first row is number one, of the row within its window partition according to the window ordering of the window.

Type

ANSI SQL:2011 window function.

Syntax  

where:

 

Syntax element …

Specifies …

OVER

the window partition and ordering.

PARTITION BY

the column, or columns, according to which the result set is partitioned.

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 partition.

PARTITION BY clause is also called the window partition clause.

ORDER BY

in its value_expression the order in which to sort the values in the partition.

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.

ANSI Compliance

This is ANSI SQL:2011 compliant.

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

Window Aggregate Equivalent

   ROW_NUMBER() OVER (PARTITION BY column ORDER BY value) 

is equivalent to

   COUNT(*) OVER (PARTITION BY column ORDER BY value 
   ROWS UNBOUNDED PRECEDING). 

For more information on COUNT, see “Window Aggregate Functions” on page 984.

Example  

To order salespersons based on sales within a sales region, the following SQL query might yield the following results.

   SELECT ROW_NUMBER() OVER (PARTITION BY sales_region 
                             ORDER BY sales_amount DESC),
   sales_person, sales_region, sales_amount
   FROM sales_table;
   
   Row_Number()  sales_person  sales_region  sales_amount
   ------------  ------------  ------------  ------------
              1  Baker         East                   100
              2  Edwards       East                    99
              3  Davis         East                    89
              4  Adams         East                    75
              1  Garabaldi     West                   100
              2  Connors       West                    99
              3  Fine          West                    99