extracting top 5 from a table 1

[jimberger]

Hi,

I have a complex SQL query that i cannot solve in access. I have a table, for example

john
james
sam
james
fred
harry
james
etc..

and i want to be able to define a SQL statement that will give a the top 5 most popular elements..i have a SQL statement

SELECT TOP 1 productID FROM orders GROUP BY productID ORDER BY count(productID) DESC

that will give me the most popular element but how do i get the sql statement to return the top 5. any ideas?
thanks for your time
jim

 

[PHV]

What about this ?
SELECT TOP 5 productID FROM orders GROUP BY productID ORDER BY count(productID) DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldFoxtrot]

Try a 2-step approach first. 1) SELECT productID count(productID) FROM orders GROUP BY productID. Make it a query. 2) From the first query, SELECT productID countx ORDER BY countx DESC WHERE COUNTX <= 5. Note, the above is not real syntax, just a logic. COUNTX is the name of the count field in query 1. You may be able to combine them using a sub-query. Let me know how you make out.
Dave