Select TOP n rows for each group in T-SQL

Select the TOP n Rows For Each Group
There are Multiple Rows for Each Category, and there is a desire to SELECT ONLY the TOP two (2) Rows per Category by Price.


SQL Server 2005 / 2008 using Partition:

--Query to Retrieve Desired Data
SELECT RowID,Category,[ID],[Description],Price FROM
(SELECT ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
RowID,Category,[ID],[Description],Price FROM @MyTable) dt
WHERE RowNumber <= 2  -- Results RowID Category ID Description Price  11 Bowl C1 Lg Mixing 27.50  12 Bowl C2 Sm Mixing 17.50  9 Pan B3 Bundt Cake 12.50  10 Pan B4 9x12 Brownie 7.95  6 Pot A6 Pressure Cooker 79.95  5 Pot A5 Stewpot 49.50  13 Tools T1 14" Spatula 9.95

SQL Server 2005 / 2008 using CTE

-- Define a CTE with the name "dt"
;WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY Category ORDER BY Price DESC ) AS 'RowNumber',
RowID,Category,[ID],[Description],Price FROM @MyTable )
-- and select the data from the CTE
SELECT
RowID,Category,[ID],[Description],Price FROM dt
WHERE RowNumber <= 2  -- Results  RowID Category ID Description Price  11 Bowl C1 Lg Mixing 27.50  12 Bowl C2 Sm Mixing 17.50  9 Pan B3 Bundt Cake 12.50  10 Pan B4 9x12 Brownie 7.95  6 Pot A6 Pressure Cooker 79.95  5 Pot A5 Stewpot 49.50  13 Tools T1 14" Spatula 9.95 

SQL Server 2000:

--Query to Retrieve Desired Data
SELECT DISTINCT
RowID,Category,[ID],[Description],Price FROM @MyTable t1
WHERE RowID IN (SELECT TOP 2
RowID FROM @MyTable t2 WHERE t2.Category = t1.Category ORDER BY Price DESC)
ORDER BY Category,Price DESC

-- Results
RowID Category ID Description Price
11 Bowl C1 Lg Mixing 27.50
12 Bowl C2 Sm Mixing 17.50
9 Pan B3 Bundt Cake 12.50
10 Pan B4 9x12 Brownie 7.95
6 Pot A6 Pressure Cooker 79.95
5 Pot A5 Stewpot 49.50
13 Tools T1 14" Spatula 9.95