QUALIFY Clause in Redshift – Alternative and Examples

Post author:Vithal S
Post last modified:April 5, 2021
Post category:Redshift
Reading time:4 mins read

If you are migrating from Teradata to Redshift, you will notice that many features are missing in Amazon Redshift. The feature such as QUALIFY clause is widely used in Teradata. But, the same feature is not available in Redshift. In this article, we will check what is QUALIFY Clause alternative in AWS Redshift.

QUALIFY Clause in Redshift

The relational databases such as Teradata uses QUALIFY clause to filter the result of ordered analytical function. Amazon Redshift does not support this feature yet. But, when you are migrating Teradata scripts to Redshift, you may have to identify the different approach.

Before jumping into alternative approach, let us first check little bit about QUALIFY clause.

QUALIFY Clause

A conditional clause in the SELECT statement that filters results of a previously computed ordered analytical function according to user‑specified search conditions.

The following example uses analytical functions and a QUALIFY clause.

SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID)
   FROM facts
   QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;

The QUALIFY is a conditional clause that, similar to HAVING, further filters rows from a WHERE clause. The major difference between QUALIFY and HAVING is that with QUALIFY the filtering is based on the result of performing various ordered analytical functions on the data.

QUALIFY Clause Alternative in Redshift

In Amazon Redshift, you can use subquery and WHERE clause as QUALIFY clause alternative to filter the result of ordered analytical windows functions.

Following alternative syntax will work on Redshift without any issues.

SELECT StoreID, auto_01
FROM(SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID) as auto_01,
 SUM(profit) OVER (PARTITION BY StoreID) as rn
   FROM facts) as T
   WHERE rn > 2;

As you can see, windows function in a QUALIFY clause is treated as a column inside the subquery and you can use that column in the WHERE clause of the upper query to filter records.