> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# QUALIFY Clause in SQL

<Note>
  * The `QUALIFY` clause filters the results of window functions.
  * This filtering of results is similar to how a `HAVING` clause filters the results of aggregate functions applied based on the `GROUP BY` clause.
  * A window function is required to be present either in the `QUALIFY` clause or the `SELECT` list.
  * The QUALIFY clause avoids the need for a subquery or WITH clause to perform this filtering (much like HAVING avoids a subquery).
</Note>

The evaluation of a query with a `QUALIFY` clause is typically completed in this order:

1. `FROM`
2. `WHERE`
3. `GROUP BY` and aggregation
4. `HAVING` (filters the results of aggregation)
5. `WINDOW`
6. `QUALIFY` (filters the results of window functions)
7. `DISTINCT`
8. `ORDER BY`
9. `LIMIT`

## Example

Lets create a table `employee` with the following data:

```sql theme={"system"}
CREATE TABLE employee (
    emp_name STRING, 
    job_title STRING, 
    city STRING, 
    salary INT
);

INSERT INTO employee VALUES
    ('Amit', 'Software Engineer', 'Mumbai', 600000),
    ('Raja', 'Data Scientist', 'Mumbai', 850000),
    ('Suresh', 'HR Manager', 'Mumbai', 450000),
    ('Vikram', 'Software Engineer', 'Bangalore', 550000),
    ('Megha', 'Data Scientist', 'Bangalore', 800000),
    ('Anjali', 'HR Manager', 'Bangalore', 400000),
    ('Rohit', 'Software Engineer', 'Delhi', 500000),
    ('Priya', 'Data Scientist', 'Delhi', 750000),
    ('Kavita', 'HR Manager', 'Delhi', 420000);
```

### Find Employees with Lowest Salary for Each Job Title

**Using CTE:**

```sql theme={"system"}
WITH ranked_employees AS (
    SELECT
        emp_name,
        job_title,
        city,
        salary,
        RANK() OVER (PARTITION BY job_title ORDER BY salary) AS rank
    FROM employee
)
SELECT emp_name, job_title, city, salary
FROM ranked_employees
WHERE rank = 1;
```

**Output:**

| emp\_name | job\_title        | city      | salary |
| --------- | ----------------- | --------- | ------ |
| Rohit     | Software Engineer | Delhi     | 500000 |
| Megha     | Data Scientist    | Bangalore | 800000 |
| Anjali    | HR Manager        | Bangalore | 400000 |

**Using Sub-query:**

```sql theme={"system"}
SELECT emp_name, job_title, city, salary
FROM (
    SELECT
        emp_name,
        job_title,
        city,
        salary,
        RANK() OVER (PARTITION BY job_title ORDER BY salary) AS rank
    FROM employee
) AS ranked_employees
WHERE rank = 1;
```

**Output:**

| emp\_name | job\_title        | city      | salary |
| --------- | ----------------- | --------- | ------ |
| Rohit     | Software Engineer | Delhi     | 500000 |
| Megha     | Data Scientist    | Bangalore | 800000 |
| Anjali    | HR Manager        | Bangalore | 400000 |

**Using `QUALIFY` clause:**

```sql theme={"system"}
/* Window function in QUALIFY clause*/
SELECT emp_name, job_title, city, salary
FROM employee
QUALIFY RANK() OVER (PARTITION BY job_title ORDER BY salary) = 1;

```

**Output:**

| emp\_name | job\_title        | city      | salary |
| --------- | ----------------- | --------- | ------ |
| Rohit     | Software Engineer | Delhi     | 500000 |
| Megha     | Data Scientist    | Bangalore | 800000 |
| Anjali    | HR Manager        | Bangalore | 400000 |

Different ways to write the same query using `QUALFIY` clause to get the same result.

<AccordionGroup>
  <Accordion title="Window function in SELECT column list">
    ```sql theme={"system"}
    SELECT emp_name, job_title, city, salary,
    RANK() OVER (PARTITION BY job_title ORDER BY salary) AS emp_rank
    FROM employee
    QUALIFY emp_rank = 1;
    ```
  </Accordion>

  <Accordion title="Window function in the QUALIFY clause but using WINDOW clause">
    ```sql theme={"system"}
    SELECT emp_name, job_title, city, salary
    FROM employee
    WINDOW w AS (PARTITION BY job_title ORDER BY salary)
    QUALIFY RANK() OVER(w) = 1;
    ```
  </Accordion>

  <Accordion title="Window function in the SELECT clause but using WINDOW clause">
    ```sql theme={"system"}
    SELECT emp_name, job_title, city, salary,
    RANK() OVER (w) AS emp_rank
    FROM employee
    WINDOW w AS (PARTITION BY job_title ORDER BY salary)
    QUALIFY emp_rank = 1;
    ```
  </Accordion>
</AccordionGroup>

<Warning> `QUALIFY` is not part of the ANSI SQL standard. So it is not supported in all databases.</Warning>

QUALFIY clause is a powerful feature in SQL that can be used to filter the results of window functions without the need for a subquery or WITH clause. It is a great way to simplify the SQL query and make it more readable. Since it is not part of the ANSI SQL standard, it is not supported in many databases like SQL server, [spark](https://github.com/apache/spark/pull/39691).
Currently supported in [databricks](https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-qualify.html), [snowflake](https://docs.snowflake.com/en/sql-reference/constructs/qualify), [bigquery](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause), [teradata](https://docs.teradata.com/r/2_MC9vCtAJRlKle2Rpb0mA/ARrPVQ98tH__I2CfJZ62oQ) and [duckdb](https://duckdb.org/docs/sql/query_syntax/qualify.html).