OK, another one that seems to be often asked, and I'm sure there's a SQL Team article out there...I'm just lazy.  Anyway, this post got me interested in this again (and I always have trouble trying to remember the answer), so I decided to post some solutions, so I don't have to remember. With the Help of Pat Phelan, Rudy Limeback, and Mr. B. Lindman (aka the Blind Dude), we came up with the following.

Notice the differences in the Plans.  While Pat's Subquery looks cleaner, Rudy's Join seems more effecient.  Any comments on the plans would be greatly appreciated.

EDIT: Sunsande points out some other shortcomings in the SQL.  With their proposed SQL you not only get the top n results, but you do get ties.  However n top results would require dynamic sql for this.  Still I think it looks to be the best solution so far.  Thanks

DECLARE @N int, @sql nvarchar(4000)
     SET @N = 3  --The number of records to return for each grouping.

SET @sql =
 N'  SELECT a.SiteId, a.EmpId, a.Sales '
+ '    FROM myTable99 a '
+ '   WHERE a.Sales IN (SELECT DISTINCT TOP ' + CONVERT(varchar(15),@n) + ' b.Sales '
+ '         FROM myTable99 b '
+ '        WHERE a.EmpID = b.EmpId '
+ '     ORDER BY b.sales DESC) '
+ 'ORDER BY a.EmpID, a.Sales DESC, a.SiteID '

EXECUTE sp_executesql @sql

The Question here was, Show my the TOP 3 Sales for an Employee by Store

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(SiteId int, EmpId int, Sales money
  , PRIMARY KEY(EmpId, Sales, SiteId))
GO

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 1, 1, 10.00 UNION ALL
SELECT 2, 1, 15.00 UNION ALL
SELECT 3, 1, 20.00 UNION ALL
SELECT 4, 1, 50.00 UNION ALL
SELECT 5, 1, 10.00 UNION ALL
SELECT 6, 1, 5.00 UNION ALL
SELECT 1, 2, 100.00 UNION ALL
SELECT 2, 2, 1500.00 UNION ALL
SELECT 3, 2, 2000.00 UNION ALL
SELECT 4, 2, 5000.00 UNION ALL
SELECT 5, 2, 1000.00 UNION ALL
SELECT 6, 2, 500.00 UNION ALL
SELECT 1, 3, 1.00 UNION ALL
SELECT 2, 3, 1.50 UNION ALL
SELECT 3, 3, 2.00 UNION ALL
SELECT 4, 3, 5.00 UNION ALL
SELECT 5, 3, 1.00 UNION ALL
SELECT 6, 3, .50
GO

SET SHOWPLAN_TEXT ON
GO

DECLARE @N int
     SET @N = 3  --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId, a.Sales
   FROM myTable99 a
   WHERE (SELECT Count(*)
      FROM myTable99 b
      WHERE  b.EmpId   = a.EmpId
         AND a.Sales  <= b.Sales) <= @N
   ORDER BY a.EmpID, a.Sales DESC, a.SiteID

    SELECT  a.EmpId, a.SiteId, a.sales
      FROM myTable99 a
INNER JOIN myTable99 b
        ON a.EmpId  = b.EmpId
       AND a.Sales <= b.Sales
  GROUP BY a.EmpId, a.SiteId, a.sales
  HAVING COUNT(*) <= @N
ORDER BY a.EmpId, a.sales desc
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO