top N per employee

[geebee]

hi,

i have a table with lots of rows. I'm trying to pull out the top 10 records
by dollar amount by employee. i tried the following:

SELECT [employee], [amount]
FROM YourTable
WHERE amount=
(SELECT Top 10 amount
FROM YourTable as Temp
WHERE Temp.[employee] = YourTable.[employee]
ORDER BY amount DESC)

am i on the right track? what i want to retrieve is every employee, and the
top 10 amounts per employee.

thanks in advance,
geebee

 

[Klatuu]

Try:
SELECT [employee], [amount]
FROM YourTable
WHERE amount IN
((SELECT Top 10 amount
FROM YourTable as Temp
WHERE Temp.[employee] = YourTable.[employee]
ORDER BY amount DESC));