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;
emp_idemp_nameemp_cityrecord_insert_ts
P101MarkAmsterdam2022-08-14 14:14:08.174
P101MarkParis2022-08-14 14:17:16.304
P101MarkLondon2022-08-14 14:21:42.557
P102JerryAmsterdam2022-08-14 14:14:08.174
P103SophiaAmsterdam2022-08-14 14:14:08.174
P103SophiaBerlin2022-08-14 14:17:16.304
P104BernardAmsterdam2022-08-14 14:14:08.174
P105AmandaAmsterdam2022-08-14 14:14:08.174
P106MarieAmsterdam2022-08-14 14:17:16.304
P107JamieAmsterdam2022-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_idemp_nameemp_cityrecord_insert_ts
P101MarkLondon2022-08-14 14:21:42.557
P102JerryAmsterdam2022-08-14 14:14:08.174
P103SophiaBerlin2022-08-14 14:17:16.304
P104BernardAmsterdam2022-08-14 14:14:08.174
P105AmandaAmsterdam2022-08-14 14:14:08.174
P106MarieAmsterdam2022-08-14 14:17:16.304
P107JamieAmsterdam2022-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

Leave a Reply

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