- By Itzik Ben-Gan
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. For example, Teradata 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 the SQL standard; 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.