TSQL : TOP N PER GROUP using RANK()

An Adventureworks quick script to demonstrate top items in a group. It isn't the query I wrote today, but it is one I can publish here!

-- Sales Order ID, Name and Price for the 3 most expensive items in each order
-- We join to a derived table utilising the RANK function to order the items in each order by expense.
-- The WHERE clause then restricts the results according to the output of the RANK function
SELECT 
  S.SalesOrderID
 ,P.Name
 ,D.UnitPrice
FROM Sales.SalesOrderHeader S
INNER JOIN
 (SELECT 
  RANK() OVER (PARTITION BY SalesOrderID ORDER BY UnitPrice DESC) ItemCount
  ,* 
  FROM Sales.SalesOrderDetail)  D
ON S.SalesOrderID = D.SalesOrderID
INNER JOIN Production.Product P
ON P.ProductId = D.ProductId
WHERE ItemCount <= 3
ORDER BY SalesOrderID, Name, UnitPrice 

Solace : Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

Luke Hayler : Rediscovering RANK