How to use a CTE to select the first n rows of a referenced table for every row

The Common Table Expression (CTE) feature introduced in SQL Server 2005 provides some very useful querying extensions (albeit these are obviously not ANSI-92 compliant). A CTE takes the following form:

WITH cte AS
(
SELECT col1
FROM dbo.tbl1
WHERE...
)
SELECT col1
FROM dbo.cte
WHERE...
GO

One useful adjunct to the CTE is the ROW_NUMBER function in combination with the PARTITION BY clause. This allows you to write a query returning one or more rows of a subquery without resorting to complicated correlated subqueries and performance killing TOP n predicates. For example, say you want to pull for every row in a parent table the first row in a child table. You can write this query using a CTE as follows:

WITH firstChild AS
(
SELECT ParentID, ChildID,
ROW_NUMBER() OVER(PARTITION BY ParentID ORDER BY ChildID ASC) AS rn
FROM dbo.tblChildren c
)
SELECT p.ParentID, fc.ChildID
FROM dbo.tblParents p
INNER JOIN firstChild fc ON p.ParentID = fc.ParentID AND fc.rn = 1;
GO

The performance of this query is blindingly fast because ROW_NUMBER is being performed in memory and has no disk IO cost. You can also use this technique to generate high performance crosstab queries.