This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Download Microsoft Edge More info about Internet Explorer and Microsoft Edge

Applies to: Databricks SQL Databricks Runtime 10.0 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
boolean_expression
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 QUALIFY clause cannot contain aggregate functions.
Examples
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
Related statements
SELECT
WHERE clause
GROUP BY clause
ORDER BY clause
SORT BY clause
CLUSTER BY clause (SELECT)
DISTRIBUTE BY clause
LIMIT clause
PIVOT clause
LATERAL VIEW clause
			Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.		

        
Submit and view feedback for
                    This product