Wed 10 Feb 2010
Teradata offers the ability to build a query, and then use a QUALIFY statement that filters the resulting resultset. This is especially handy when using a ROW_NUMBER function where you are partitioning on a natural key (or whatever it may be) and you want to return only the rows where the ROW_NUMBER value is 1, for example.
In SQL Server today, this has to be done with derived tables/CTEs like so:
USE AdventureWorksDW
GO
/* Ignoring the fact that this table
has a "Status" and an EndDate column */
;WITH CTE AS (
SELECT EmployeeKey,
FirstName,
LastName,
MiddleName,
Title,
ROW_NUMBER() OVER (PARTITION BY EmployeeNationalIDAlternateKey
ORDER BY StartDate DESC) AS ROW_NUM
FROM dbo.DimEmployee
)
SELECT EmployeeKey,
FirstName,
LastName,
MiddleName,
Title
FROM CTE
WHERE row_num = 1
ORDER BY FirstName, LastName
GO
That’s easy enough to understand, of course, but the problem lies in the fact that you have to write a bunch of extra lines of code to accomplish this task.
Teradata has the QUALIFY option which would be represented like this:
SELECT EmployeeKey,
FirstName,
LastName,
MiddleName,
Title
FROM dbo.DimEmployee
QUALIFY ROW_NUMBER() OVER (PARTITION BY EmployeeNationalIDAlternateKey
ORDER BY StartDate DESC) = 1
Note how much cleaner that looks. QUALIFY has many advantages, but I’ll leave that as an exercise for you to review. In the mean time, if you would like to see such an option in SQL Server, please vote on Itzik Ben-Gan’s feature request: https://connect.microsoft.com/SQLServer/feedback/details/532474/qualify-request-for-a-new-filtering-clause
