QUALIFY clause
Applies to: 
Databricks SQL Databricks Runtime 10.4 LTS and above.
Filters the results of window functions. To use QUALIFY, at least one window function is required to be present in the SELECT list or the QUALIFY clause.
Syntax
QUALIFY boolean_expression
Parameters
-
Any expression that evaluates to a result type
boolean. Two or more expressions may be combined together using the logical operators ( AND, OR).The expressions specified in the
QUALIFYclause cannot contain aggregate functions.
Examples
SQL
CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer VALUES
(100, 'Fremont', 'Honda Civic', 10),
(100, 'Fremont', 'Honda Accord', 15),
(100, 'Fremont', 'Honda CRV', 7),
(200, 'Dublin', 'Honda Civic', 20),
(200, 'Dublin', 'Honda Accord', 10),
(200, 'Dublin', 'Honda CRV', 3),
(300, 'San Jose', 'Honda Civic', 5),
(300, 'San Jose', 'Honda Accord', 8);
-- QUALIFY with window functions in the SELECT list.
> SELECT
city,
car_model,
RANK() OVER (PARTITION BY car_model ORDER BY quantity) AS rank
FROM dealer
QUALIFY rank = 1;
city car_model rank
-------- ------------ ----
San Jose Honda Accord 1
Dublin Honda CRV 1
San Jose Honda Civic 1
-- QUALIFY with window functions in the QUALIFY clause.
SELECT city, car_model
FROM dealer
QUALIFY RANK() OVER (PARTITION BY car_model ORDER BY quantity) = 1;
city car_model
-------- ------------
San Jose Honda Accord
Dublin Honda CRV
San Jose Honda Civic