Top n per group

[jamesdpmullan]

Hi,

I have a bit of an issue with this top N per group which i understand has been talked about on many occasions however i just cannot get this to work.

The code i have entered is as follows:

SELECT [1QryComplexityDelivery4].Year, [1QryComplexityDelivery4].Month, [1QryComplexityDelivery4].YearandMonth, [1QryComplexityDelivery4].Customer_Code, [1QryComplexityDelivery4].SumOfValueDespatched
FROM [1QryComplexityDelivery4]
WHERE [1QryComplexityDelivery4].SumOfValueDespatched IN
(SELECT TOP 10 SumOfValueDespatched
FROM [1QryComplexityDelivery4] AS test
WHERE test.yearandmonth = [1QryComplexityDelivery4].yearandmonth
ORDER BY test.sumofvaluedespatched DESC)
ORDER BY [1QryComplexityDelivery4].Year DESC , [1QryComplexityDelivery4].Month DESC , [1QryComplexityDelivery4].SumOfValueDespatched DESC;


I run the query and it just doesnt run - it seems to lock. I suspect the above coding is incorrect so hoping someone might help. The list below shows a snapshot of the data

Year Month YearandMonth Customer_Code SumOfValueDespatched
2013 10 201310 SIE07 £154,890.19
2013 10 201310 ALS01 £24,412.18
2013 10 201310 TBP77 £24,381.83
2013 10 201310 FER01 £15,988.80
2013 10 201310 TIS02 £15,860.00
2013 10 201310 STI01 £11,563.00
2013 10 201310 NEM01 £8,804.10
2013 10 201310 CEG01 £6,852.76
2013 10 201310 GECR1 £6,034.00
2013 10 201310 AXI01 £5,601.46
2013 10 201310 BRI01 £5,544.56
2013 10 201310 COT02 £5,018.10
2013 10 201310 DRU01 £4,720.38
2013 10 201310 CML99 £4,622.33
2013 10 201310 FAC01 £3,471.82
2013 10 201310 BLA01 £2,859.84
2013 10 201310 JAB08 £2,782.00
2013 10 201310 AMC01 £2,776.30
2013 10 201310 WES03 £2,493.10
2013 10 201310 NOR14 £2,462.50
2013 10 201310 KEN10 £2,402.00
2013 10 201310 HAR05 £2,205.00
2013 10 201310 ELM02 £2,037.00
2013 10 201310 CEL55 £1,908.00
2013 10 201310 VEN01 £1,710.00
2013 10 201310 ARD99 £1,672.46
2013 10 201310 ASK01 £1,425.00
2013 10 201310 ONT01 £1,313.04
2013 10 201310 CON05 £1,300.00
2013 10 201310 JAB01 £1,290.00
2013 10 201310 OMI99 £1,251.48
2013 10 201310 FTT01 £1,195.20
2013 10 201310 ERN99 £1,170.10
2013 10 201310 FIS77 £1,160.00
2013 10 201310 AMS02 £1,137.50
2013 10 201310 GEC01 £1,120.00
2013 10 201310 KEY01 £1,100.00
2013 10 201310 RST01 £1,025.00
2013 10 201310 ULT04 £1,020.00
2013 10 201310 ZOL01 £1,000.00
2013 10 201310 ELM12 £970.00
2013 10 201310 DEL05 £940.00
2013 10 201310 KEL04 £920.00
2013 10 201310 PAR07 £850.00
2013 10 201310 GPS01 £812.00
2013 10 201310 IFR02 £802.00
2013 10 201310 ACT02 £667.00
2013 10 201310 FOU01 £635.00
2013 10 201310 GYR01 £600.00
2013 10 201310 PAG01 £600.00
2013 10 201310 MCL01 £562.50
2013 10 201310 ULT01 £550.05
2013 10 201310 MRP01 £543.12
2013 10 201310 AWS01 £500.00
2013 10 201310 REU02 £448.00
2013 10 201310 QUA01 £420.00
2013 10 201310 SSG01 £385.80
2013 10 201310 ABE02 £138.00
2013 10 201310 AXI02 £0.00
2013 10 201310 MAT01 £0.00
2013 9 20139 SIE07 £199,274.19
2013 9 20139 TBP77 £40,832.61
2013 9 20139 ALS01 £37,994.00
2013 9 20139 FAC01 £34,553.08
2013 9 20139 WES03 £17,668.28
2013 9 20139 TIS02 £16,250.00
2013 9 20139 CML99 £14,947.42

 

[jdraw]

see this link, then look for Top N
http://allenbrowne.com/subquery-01.html

 

[jamesdpmullan]

Hi

Thanks for your response - iv tried following this to the letter however it just doesn't seem to work - i have hundreds of queries and forms but its just this top n per group which is causing me some problems

 

[jdraw]

In plain English for what GROUP do you want top 10? You have not selected Customer_code, so what grouping is involved?

Also why do you record Year and Month in separate fields? And alos use YearMonth combined? Sounds like you may have a table structure issue.

 

[jamesdpmullan]

The year and month are set from date despatched.

The year and month is then linked to many other tables to gather data which is dependant on year and month.

The yearandmonth combined field is the GROUP.

Since the last post, i have managed to get this working through the same code posted originally however the query is exceptionally slow

Access 2013 is what i am using.

Customer code has not been selected as i am trying to obtain the 10 largest despatch values per monthandyear (hence the merging of these two)

Thanks

 

[jdraw]

Glad you have it working.