How to use Qualify Row_number in Teradata?

Row_number() function in Teradata

Row_number function is used to assign the sequential number to each row of the result set that is selected from a table or joined tables in Teradata. The first row has a row number of 1, second row has a row number of 2 , and so on.

Syntax

OVER :

OVER clause defines the window partition or user specified set of rows within a query result set.

PARTITION BY:

The PARTITION BY clause breaks the result set into smaller sets based on the column reference. It is optional. If we didn’t specify the Partitioned by in the query , the entire result is considered a partition.PARTITION BY clause is also called the window partition clause.

ORDER BY:

After PARTITIONED BY clause, we can define the Value expression to sort the window partition values. ORDER BY clause is used to sort those values within each partition of the result set.

ASC/DESC

The partitioned results are ordered in either Ascending or Descending using ASC/DESC command.

Example for Row number function

Lets see the use of row number function with examples. The below table Exam_Results contains the students marks details for the subject of Mathematics and Science.

Sample Table : Exam_Results

Row number function example in Teradata
Row number function example in Teradata

Using the Row_number function, we can order the Students Names based on Marks with in each subjects such as Mathematics and Science.

Row number Query

Since we have applied the Partition to Subject column in the Exam_Results table, Mathematics and Science are treated as separate partitions. Then the marks are ordered in descending order within each partition and returned the result set with row number and student name as below.

Output of Row number function

Insert row number in Teradata
Insert row number in Teradata

Qualify clause in Teradata

The Qualify clause is used to filter the results of ordered analytical function according to user‑specified search conditions. We can use this conditional clause in the SELECT statement to get the particular order values.

Example for Qualify function with row number

Lets extract the student name who got the highest mark in the Mathematics and Science subjects.

Qualify and Row number in Teradata

We can use row number with qualify function to extract the required results. The Row number function ordered the marks with row number. In order to get the highest marks in each subject, we are using the Qualify function to take the the record that has row number as 1.

Output

Example for Row number with Qualify function in Teradata
Example for Row number with Qualify function in Teradata

Revisitclass is created to share the knowledge with the other people. If you would like to write an article, please mail your article to [email protected]. Your article will be posted on the Revisitclass main page. If you want to add more information about any topic in this portal,write a mail to us.