How do you use the QUALIFY clause in Teradata to filter rows based on specific conditions?

1 What is the QUALIFY clause?

The QUALIFY clause is a Teradata extension to the standard SQL syntax. It is similar to the WHERE clause, but it operates on the results of window functions, aggregate functions, or ranking functions that are applied to the query. Window functions are functions that partition the data into subsets and perform calculations on each subset. Aggregate functions are functions that summarize the data by grouping and applying operations such as SUM, COUNT, or AVG. Ranking functions are functions that assign a rank or order to each row based on some criteria.

2 How to use the QUALIFY clause?

The QUALIFY clause is placed after the SELECT, FROM, WHERE, GROUP BY, and HAVING clauses, but before the ORDER BY clause. The syntax of the QUALIFY clause is: QUALIFY condition The condition can be any logical expression that involves window functions, aggregate functions, or ranking functions. For example, you can use the QUALIFY clause to filter rows based on their rank, sum, average, or percentage.

3 How to find the top N rows with the QUALIFY clause?

One common use case of the QUALIFY clause is to find the top N rows in each group based on some measure. For example, you can use the QUALIFY clause to find the top 3 products in each category based on their sales. To do this, you can use the RANK() function to assign a rank to each product within each category, and then use the QUALIFY clause to filter only those rows that have a rank less than or equal to 3. The query would look like this:

SELECT category, product, sales
FROM sales_table
QUALIFY RANK() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;        

This query will return the top 3 products in each category based on their sales.

4 How to eliminate duplicates with the QUALIFY clause?

Another use case of the QUALIFY clause is to eliminate duplicates from the query results. For example, you can use the QUALIFY clause to remove duplicate customers based on their email address. To do this, you can use the ROW_NUMBER() function to assign a row number to each customer within each email group, and then use the QUALIFY clause to filter only those rows that have a row number equal to 1. The query would look like this:

SELECT customer_id, name, email
FROM customer_table
QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) = 1;        

This query will return only one customer for each email address.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?