Take Advantage of New T-SQL Features (Continued)

This batch (TopProductsByPrice.sql) returns two rowsets with rows for the 10 most expensive products. Adding the WITH TIES modifier includes rows having UnitPrice values that match the last row returned by the TOP expression, regardless of the numRows or pctRows value.

For example, this batch (Top11ProductsByPriceWithTies.sql) returns 12 rows because the UnitPrice values of rows 11 and 12 are the same ($43.90):

DECLARE @numRows AS bigint;
SET @numRows = 11;
SELECT TOP (@numRows) WITH TIES * 
FROM Products 
ORDER BY UnitPrice DESC
GO

The ANSI SQL99 ROW_NUMBER, RANK, and DENSE RANK functions return bigint values that correspond to the order of the rows you specify by an associated ORDER BY clause. ROW_NUMBER returns a monotonically increasing value for all rows in the specified rowset (see Figure 3). RANK and DENSE RANK return duplicate numbers for values with ties. RANK has gaps in the row number value following ties; DENSE RANK eliminates the ties. This batch returns RANK, DENSE RANK, and ROW_NUMBER values for the Products table in descending UnitPrice order:

SELECT ProductID, ProductName, UnitPrice, 
   RANK() OVER (ORDER BY UnitPrice DESC) 
      AS Rank,
   DENSE_RANK() OVER (ORDER BY UnitPrice 
      DESC) AS DenseRank,
   ROW_NUMBER() OVER (ORDER BY UnitPrice 
      DESC) AS RowNumber
FROM Products
GO

The ORDER BY clause for ROW_NUMBER returns nondeterministic values for rows with equal Unit Price values, such as 11 and 12, and 15 and 16. A non-deterministic rowset has values that can differ for successive query invocations. For example, Schoggi Schokolade and Vegie-spread are equally valid as ROW_NUMBER 11 and 12 or 12 and 11, respectively. You must add another column to the ORDER BY clause, such as ProductID ASC, to generate a deterministic rowset in which ROW_NUMBERS are guaranteed to be consistent for single-column value ties.

PARTITION BY is a windowing clause that divides a ranked resultset into groups that you specify. This batch groups rankings by product Category partitions (ProductsPartition.sql):

SELECT CategoryID, ProductID, ProductName, 
   UnitPrice, RANK() OVER (PARTITION BY 
   CategoryID ORDER BY UnitPrice DESC) AS 
   Rank, DENSE_RANK() OVER (PARTITION BY 
   CategoryID ORDER BY UnitPrice DESC) AS 
   DenseRank, ROW_NUMBER() OVER 
   (PARTITION BY CategoryID ORDER BY 
   UnitPrice DESC) AS RowNumber
FROM Products
GO

Adding PARTITION BY is similar to applying GROUP BY to the SELECT statement, but you can apply a different PARTITION BY criterion to each ranking function in the query. The ranking values start over for each PARTION BY criterion. NTILE(buckets) assigns a rank of 1 through buckets to the rows; for example, if buckets = 4, each row has a quartile value (1 through 4).

Common Table Expressions (CTEs) are temporary, in-memory tables that are easier to create and populate than conventional temporary tables. CTEs are similar to derived tables, but you can reference CTEs by name and use them more than once. CTEs enable recursive queries that you define with a UNION ALL query that combines rows from an anchor member and a recursive member (EmployeesDirectReportsCTE.sql):

WITH DirectReports (Name, EmployeeID, ReportsTo) AS
--Anchor member
(SELECT FirstName + ' ' + LastName, EmployeeID, 
ReportsTo FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--Recursive member
SELECT emp.FirstName + ' ' + emp.LastName, 
emp.EmployeeID, emp.ReportsTo
FROM Employees emp INNER JOIN DirectReports dr
ON emp.ReportsTo = dr.EmployeeID)

SELECT * FROM DirectReports;
GO

This batch returns Andrew Fuller (2), who has no manager, followed by employees who report to him and those who report to Steven Buchanan (5).