ROW_NUMBER FUNCTION IN TERADATA

Share via:
ROW_NUMBER FUNCTION IN TERADATA

Dear Readers.

In this article ,we will see ROW_NUMBER FUNCTION IN TERADATA

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,

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.

DSC                  :      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.

Example

Row_number vs Rank

Unlike RANK, the ROW_NUMBER function disregards any tied values.

If the object of ROW_NUMBER is unique, then it could be used to Rank as well.

 

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment