top N per employee

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

Try this -
**********************************************
SELECT DISTINCT
t.Employee
, YourTable.Amount
FROM
YourTable AS t
LEFT JOIN
YourTable
ON
t.Employee = YourTable.Employee
WHERE
(((YourTable.Amount) In (
SELECT
Top 10 [Amount]
FROM
YourTable
WHERE
[Employee]= t.[Employee]
ORDER BY
[Amount])))
ORDER BY
t.Employee
, YourTable.Amount DESC;
**********************************************
HTH - Bob

geebee wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1

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));


--
Dave Hargis, Microsoft Access MVP


"geebee" wrote:

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