Use of Teradata Qualify Row Number

teradata

We can use Teradata Qualify Row Number to have an alternate solution to Group by clause or using distinct. The keyword “Qualify” works on the final resultset before it is returned to the user. We can use Qualify row number in order to select a specific record or number of records or to even generate sequence numbers.

We will understand how Qualify Row Number is useful by sharing example of a case when we have SCD2 implemented in table and we want to select latest value for any column. Example, if there is an employee in a table emp who has changed his location from one city to other in the same company. For SCD2 we will add a new record with new location for the employee and date from which it is effective. If requirement is to select only the latest location for all the employees we can use Qualify Row Number to obtain the desired result.

SQL Query will be somewhat like :

SELECT
	EMP_NAME,
	EMP_LOCATION
FROM
	EMP QUALIFY ROW_NUMBER() OVER ( PARTITION BY EMP_NAME
ORDER BY
	DATE_EFFECTIVE DESC) = 1;

This query will result in getting all the EMPLOYEE NAMES with their latest location. ROW NUMBER will fetch the first record and we are ordering on the basis of date in descending order so latest record will be fetched for each employee.

Using Qualify Row Number is also very useful when we have very complex queries consisting of left , right joins involving several tables. In that case we can implement all our required rules and at the end we can refine the result set using Qualify Row Number.

The Qualify ROW_NUMBER() can be very helpful in removing Unique Primary Key Violation error while loading data into your Target Table.

Leave a Reply

Your email address will not be published. Required fields are marked *