SQL Windowing

Potential for Additional Filters

I provided a workaround in T-SQL that allows you to use window functions indirectly in query elements that don’t support those directly. The workaround is a table expression in the form of a CTE or derived table. It’s nice to have a workaround, but a table expression adds an extra layer to the query and complicates it a bit. The examples I showed are quite simple, but think about long and complex queries to begin with. Can you have a simpler solution that doesn’t require this extra layer?

With window functions, SQL Server doesn’t have a solution at the moment. It’s interesting, though, to see how others coped with this problem. Teradata for example created a filtering clause it calls QUALIFY that is conceptually evaluated after the SELECT clause. This means that it can refer to window functions directly, as in the following example:

SELECT orderid, orderdate, val
FROM Sales.OrderValues
QUALIFY RANK() OVER(ORDER BY val DESC) <= 5;

Furthermore, you can refer to column aliases defined in the SELECT list, like so:

SELECT orderid, orderdate, val,
  RANK() OVER(ORDER BY val DESC) AS rnk
FROM Sales.OrderValues
QUALIFY rnk <= 5;

The QUALIFY clause isn’t defined in standard SQL; rather, it’s a Teradata-specific feature. However, it seems like a very interesting solution, and it would be nice to see both the standard and SQL Server providing a solution to this need.