QUALIFY in sql applies filter on the output of WINDOW function used in the sql. If in the SQL query you are using window function then you can apply filter on it using QUALIFY.
QUALIFY clause is generally the last statement in the SQL query followed by "LIMIT" keyword.
Qualify row number is used very often to pick one row when multiple rows exists within the same partition.
ROW NUMBER in SQL is window function that generates numbers in sequential order. When used with QUALIFY it works as filter to limit the number of rows that are fetched from each partition.
In the example , we have employee table which is SCD Type 2. The table has following data
select emp_id, emp_name, emp_city, record_insert_ts from dim_employee_scd2 order by emp_id, record_insert_ts;
emp_id | emp_name | emp_city | record_insert_ts |
---|---|---|---|
P101 | Mark | Amsterdam | 2022-08-14 14:14:08.174 |
P101 | Mark | Paris | 2022-08-14 14:17:16.304 |
P101 | Mark | London | 2022-08-14 14:21:42.557 |
P102 | Jerry | Amsterdam | 2022-08-14 14:14:08.174 |
P103 | Sophia | Amsterdam | 2022-08-14 14:14:08.174 |
P103 | Sophia | Berlin | 2022-08-14 14:17:16.304 |
P104 | Bernard | Amsterdam | 2022-08-14 14:14:08.174 |
P105 | Amanda | Amsterdam | 2022-08-14 14:14:08.174 |
P106 | Marie | Amsterdam | 2022-08-14 14:17:16.304 |
P107 | Jamie | Amsterdam | 2022-08-14 14:21:42.557 |
If you have to fetch the latest record for each emp_id , you will generally use a subquery with MAX & GROUP BY combination.
select emp_id, emp_name, emp_city, record_insert_ts from dim_employee_scd2 des where (emp_id,record_insert_ts) in ( select emp_id, max(record_insert_ts) as record_insert_ts from dim_employee_scd2 group by emp_id );
emp_id | emp_name | emp_city | record_insert_ts |
---|---|---|---|
P101 | Mark | London | 2022-08-14 14:21:42.557 |
P102 | Jerry | Amsterdam | 2022-08-14 14:14:08.174 |
P103 | Sophia | Berlin | 2022-08-14 14:17:16.304 |
P104 | Bernard | Amsterdam | 2022-08-14 14:14:08.174 |
P105 | Amanda | Amsterdam | 2022-08-14 14:14:08.174 |
P106 | Marie | Amsterdam | 2022-08-14 14:17:16.304 |
P107 | Jamie | Amsterdam | 2022-08-14 14:21:42.557 |
Now we can achieve same result using qualify row number function in SQL.
We will create a partition by clause and then order by as per our requirement.
select emp_id, emp_name, emp_city, record_insert_ts from dim_employee_scd2 des QUALIFY row_number() over (partition by emp_id order by record_insert_ts desc) = 1 ;
So we see that we can use QUALIFY row number in SQL to replace GROUP BY + MAX subquery pattern. It is used to fetch specific rows from each partition.
Read more about similar topics:
Difference between RANK and ROW_NUMBER in Teradata
ROWS BETWEEN in Analytical Function in Teradata