Support QUALIFY in Spark SQL -- Necessary Quality of Life Feature

Virtually all the key players in this space support this quality-of-life feature, including Databricks, Snowflake, BigQuery, DuckDB, and quite a few others.

The QUALIFY feature is a necessary feature to support any migration of existing SQL scripts. Significant re-architecture of existing SQL scripts would be necessary to migrate those projects into Fabric.

-- QUALIFY results to limit only to rank() == 1

SELECT

  city,

  car_model,

  RANK() OVER (PARTITION BY car_model ORDER BY quantity) AS rank

 FROM dealer

 QUALIFY rank = 1;

References:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause

https://docs.snowflake.com/en/sql-reference/constructs/qualify

https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-qualify.html

https://duckdb.org/docs/sql/query_syntax/qualify