Qualify Row Number SQL. Solve complex queries with ease

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;

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
);

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