How to use Qualify Row_number in Teradata?
Contents
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
|
1 2 |
ROW_NUMBER() OVER (PARTITION BY <column_reference> ORDER BY <value_expression> ASC/DESC) |
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
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
|
1 2 3 4 5 6 7 8 |
SELECT ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Marks DESC) ,Student_Name, Subject, Marks FROM Students_DB.Exam_Results; |
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
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
|
1 2 3 4 5 6 |
SELECT * FROM Students_DB.Exam_Results QUALIFY ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Marks DESC) = 1; |
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
Recommended Articles
- How to write a Nested Case statement in Teradata?
- Pivot function in Teradata with examples
- REGEXP_SUBSTR function in Teradata with examples
- REGEXP_REPLACE function in Teradata with examples
Revisitclass is created to share the knowledge with the other people. If you would like to write an article, please mail your article to revisitclass@gmail.com. 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.
Your Suggestions
