TOP 5 Per Year...

[Guido]

I have the following SQL query...



SELECT TOP 5 SumOfTotal, Year, Customer
FROM Reporting_MonthlyTotalSales
GROUP BY Reporting_MonthlyTotalSales.SumOfTotal, Reporting_MonthlyTotalSales.Year, Reporting_MonthlyTotalSales.Customer
ORDER BY Reporting_MonthlyTotalSales.SumOfTotal DESC

This returns to me the TOP 5 highest annual SALES totals, by CUSTOMER ie.
1999 - 200,000 Cust 1
1999 - 180,000 Cust 25
2002 - 175,000 Cust 3
2010 - 130,000 Cust 49
2004 - 120,000 Cust 1

But what I actually want is : the TOP 5 sales totals (an thus the Top 5 Customers), for EACH YEAR.

Help gratefully received, thank you.

==================
Addendum: having posted this elsewhere and having no luck in terms of anyone suggesting an alternative to methods I have tried.... I attach a screenshot of a traditional example I have used that causes Access to get stuck in a loop.




I have also tried the following Ranking query suggested on another thread of this site... but clearly I have something wrong... because it doesnt return what is expected....


SELECT *
FROM (SELECT a1.SumOfTotal, a1.Customer, a1.Year, COUNT(*) AS CategoryRank
FROM Reporting_AnnualSalesByCustomer AS a1 INNER JOIN Reporting_AnnualSalesByCustomer AS a2
ON (a1.CUSTOMER = a2.CUSTOMER) AND (a1.SumOfTotal<= a2.SumOfTotal)
GROUP BY a1.SumOfTotal, a1.Customer, a1.Year
) AS RankingQuery
WHERE (((RankingQuery.[CategoryRank])<=5) )
ORDER BY RankingQuery.SumOfTotal, RankingQuery.Customer, RankingQuery.Year;



Hope someone can help.. having spent most of today on it... its doing my head in....!

Thank you,

[ranman256]

Use a 'report table' to store your results. Only do 1 year at a time. append the top 5 of year 2015.
then append top 5 of 2014., etc.

[June7]

Have you tried http://allenbrowne.com/subquery-01.html#TopN

Year is a reserved word - should not used reserved words as names for anything.

[CJ_London]

also in your subquery you need to alias your table so the query engine can differentiate it from the one in the main query.

Not going to write out your whole query but something like

SELECT *
FROM myTable
WHERE myField IN (SELECT TOP 5 myField FROM myTable AS T)

[orange]

crossposted and answered at
http://www.access-programmers.co.uk/forums/showthread.php?t=282217



Cross posting info

[CJ_London]

@Guido

thank you for wasting our time - I won't be wasting mine again

[Guido]

@Guido

thank you for wasting our time - I won't be wasting mine again

No need to get arsey 'mate'.
'crossposting' an issue? I wasnt aware either site is affiliated... no big deal... I had figured if more people see a thread on a multitudes of sites... you are more likely to get a response in good time...
provided either site is updated to advise of a successful outcome...it aint a big deal... well for most people anyway.

Being in the UK needing to sleep and not monitoring the threads every few minutes, I was about to update this link this morning to advise that it had been answered on the other site... but you got here first...

In any case, thank you all for your responses.....

[CJ_London]

read the link provided by Orange