Pulling the Top 3 Values for each Group

[kristyspdx]

Hello,



Can someone tell me how to return the top 3 values on the SumofNet Cost field below. So, I want to pull only the the Top 3 for July, the Top 3 for August, the Top 3 for Sept, etc. Below is what the query currently returns. Name of the query is Q 3: Group Transactions by Employee. Thanks!

Employee Number	Last Name	First Name	Month End	SumOfNet Cost
1	EMPLOYEE	1	31-Jul-13	$268.74
2	EMPLOYEE	2	31-Jul-13	$197.33
3	EMPLOYEE	3	31-Jul-13	$50.40
4	EMPLOYEE	4	31-Jul-13	$48.39
5	EMPLOYEE	5	31-Jul-13	$32.84
6	EMPLOYEE	6	31-Jul-13	$31.83
7	EMPLOYEE	7	31-Jul-13	$24.37
5	EMPLOYEE	5	31-Jul-13	$15.73
5	EMPLOYEE	5	31-Jul-13	$7.64
10	EMPLOYEE	10	31-Aug-13	$5,126.08
2	EMPLOYEE	2	31-Aug-13	$2,736.13
12	EMPLOYEE	12	31-Aug-13	$2,733.40
13	EMPLOYEE	13	31-Aug-13	$2,595.55
14	EMPLOYEE	14	31-Aug-13	$2,334.82
15	EMPLOYEE	15	31-Aug-13	$1,618.57
16	EMPLOYEE	16	31-Aug-13	$1,458.50
7	EMPLOYEE	7	31-Aug-13	$1,422.81
18	EMPLOYEE	18	31-Aug-13	$1,329.47
19	EMPLOYEE	19	31-Aug-13	$1,281.22
20	EMPLOYEE	20	31-Aug-13	$1,217.85
10	EMPLOYEE	10	30-Sep-13	$13,293.81
59	EMPLOYEE	59	30-Sep-13	$7,149.12
60	EMPLOYEE	60	30-Sep-13	$6,945.00
14	EMPLOYEE	14	30-Sep-13	$6,087.00
62	EMPLOYEE	62	30-Sep-13	$6,082.70
63	EMPLOYEE	63	30-Sep-13	$5,535.90
64	EMPLOYEE	64	30-Sep-13	$4,562.95
65	EMPLOYEE	65	30-Sep-13	$4,371.80
66	EMPLOYEE	66	30-Sep-13	$3,994.15
67	EMPLOYEE	67	30-Sep-13	$3,841.13
68	EMPLOYEE	68	30-Sep-13	$3,730.62
69	EMPLOYEE	69	30-Sep-13	$3,585.19
70	EMPLOYEE	70	30-Sep-13	$3,480.94
71	EMPLOYEE	71	30-Sep-13	$3,125.06

[orange]

There is a discussion and example of TOP N here

[kristyspdx]

I tried that. I get a syntax error. My SQL skills are limited. Here is what I have. Any ideas on what is wrong?

SELECT [Q 3: Group Transactions by Employee].[Employee Number], [Q 3: Group Transactions by Employee].[Last Name], [Q 3: Group Transactions by Employee].[First Name], [Q 3: Group Transactions by Employee].[Hierarchy 4], [Q 3: Group Transactions by Employee].[Hierarchy 5], [Q 3: Group Transactions by Employee].[Month End], [Q 3: Group Transactions by Employee].[SumOfNet Cost]
FROM [Q 3: Group Transactions by Employee]
WHERE [Q 3: Group Transactions by Employee].[SumOfNet Cost] IN
(SELECT TOP 3 SumOfNet Cost
FROM Q 3: Group Transactions by Employee AS Dupe
WHERE Dupe.Month End = Q 3: Group Transactions by Employee.Month End
ORDER BY Dupe.Month End DESC, Dupe.SumOfNet Cost DESC)
ORDER BY Q 3: Group Transactions by Employee.Month End, Q 3: Group Transactions by Employee.Month End, Q 3: Group Transactions by Employee.SumOfNet Cost;