How to do a Max Count in a query

[PJFry]

I am working on a project where I want to select the max count from a table.
I can do it by writing a subquery to group and count the items and then a
query to select the max count.

Here is the subquery, named sqCntOrders:
SELECT Count(order_id) as Cnt FROM tOrders
GROUP BY order_id;

And here is the query that pulls the max value from the query above
SELECT Max(sqCntOrders.Cnt) AS MaxOfCnt
FROM sqCntOrders;

How can I combine these into a single query, if I can at all?

The end result is going to be a VBA recordset where that max value is passed
to be used in a case statement.

I would also be interested in hearing a simpler way to do this

I am using A2007.

Thanks!
PJ

 

[Michel Walsh]

Your way to do it is the right way, since you have two different levels of
GROUPing.

You can try:

SELECT MAX(cnt)
FROM ( SELECT COUNT(order_id) AS cnt
FROM tOrders
GROUP BY order_id) AS x


but it does not work in some cases.



In Northwind:

SELECT Max(cnt)
FROM (SELECT COUNT(*) as cnt
FROM orders
GROUP BY employeeid) AS x


works, as example.



Vanderghast, Access MVP

 

[Dale Fye]

PJ,

Need a little more info. If your Orders table (tOrders) is anything like
mine, then the Order_ID field is the primary key, and the highest count you
would ever get of the (Order_ID) field would be 1. So you table structure
must be a little different from mine. Can you provide us with more detail
regarding your table structure?

As an example, if you had an orders detail table (tbl_Order_Details), and
you wanted to find the order with the most number of items, then you might
write your query something like:

SELECT TOP 1 Order_ID, SUM(Qty) as ItemsPurchased
FROM tbl_Order_Details
GROUP BY Order_ID
ORDER BY Sum(Qty) DESC
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[PJFry]

Dale,

The SELECT TOP was exactly what I needed.

Thanks!