SQL MAX(COUNT(*)) GROUP BY Alternatives?

sql max count per group
sql group by
sql max count subquery
sql count max occurrences
sql having max(count)
finding the highest count in a group by query
sql max group by
max count mysql

I've seen many topics about this and none of them is what I'm looking for. Say we have this simple table:

CREATE TABLE A ( 
    id INT, 
    date DATETIME
);

I want to retrieve the MAX value after grouping. So I do it as follow:

DECLARE @tmpTable TABLE(id INT, count INT);
INSERT INTO @tmpTable SELECT id, COUNT(*) FROM A GROUP BY id;
SELECT MAX(count) FROM @tmpTable;

Is there a better way of doing that? I've seen a solution in a book that I'm reading that they do it as follows:

SELECT MAX(count) FROM (SELECT COUNT(*) AS count FROM A GROUP BY id);

But this won't work :/ Could be that it works in newer T-SQL servers? Currently I'm using 2008 R2.


You also need to add an alias name for your sub query. Try like:

SELECT MAX(sub.count1) FROM (SELECT COUNT(*) AS count1 FROM A GROUP BY id) sub;

Can I use MAX(COUNT()) in SQL? - Quynh Nhu To Tuan, I came across an interesting SQL challenge that looked easy first and MAX on COUNT(occupation) and add the year to the GROUP BY. So how can we find an alternative to the MAX(COUNT()) that we are dreaming of? SQL max () with group by on two columns. To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the 'customer' table with the following condition -. 1. the combination of 'cust_country' and 'cust_city' column should make a group, the following SQL statement can be used : SELECT cust_city, cust_country, MAX( outstanding_amt ) FROM customer GROUP BY cust_country, cust_city;


You can make use of TOP

SELECT TOP 1 Id,COUNT(*) AS MAXCOUNT
FROM A
GROUP BY Id
ORDER BY MAXCOUNT DESC

If you wants the result with same max count use TOP WITH TIES

SELECT TOP 1 WITH TIES Id,COUNT(*) AS MAXCOUNT
FROM A
GROUP BY Id
ORDER BY MAXCOUNT DESC

SQL MAX() with HAVING, WHERE, IN, The usage of WHERE clause along with SQL MAX() have also described in this page. MAX(outstanding_amt) FROM customer GROUP BY cust_country, SELECT agent_code,COUNT(agent_code),MAX(ord_amount)  SQL MAX() and COUNT() with HAVING. To get data of 'agent_code', and number of agents for each group of 'agent_code' from the orders table with the following conditions - 'agent_code' for a group will be equal to the result of an outer query [SELECT MAX(agent_code)..] with following condition -


Is there a better way of doing that?

We could try using analytic functions:

WITH cte AS (
    SELECT id, COUNT(*) cnt, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rn
    FROM A
    GROUP BY id
)

SELECT cnt
FROM cte
WHERE rn = 1;

This approach is to turn out a row number, ordered descending by the count, during your original aggregation query by id. The id with the highest count then should be the first record (and this result should hold valid even if more than one id be tied for the highest count).

Regarding your original max query, see the answer by @apomene, and you are just missing an alias.

SQL MAX count() and having, The sql having also be used with sql max function. COUNT(agent_code) mycount FROM orders GROUP BY agent_code] with following  The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.


GROUP BY and HAVING Clause in SQL, Generally, these functions are aggregate functions such as min(),max(),avg(), count(), and sum() to combine into single or multiple columns. It  The GROUP BY clause groups records into summary rows. GROUP BY returns one records for each group. GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc. GROUP BY can group by one or more columns.


Limit WHERE to MAX() & COUNT(), * unconditional plus another SELECT event_id, MAX(), COUNT() FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX() and merge everything within the  In this article we have discussed how SQL HAVING CLAUSE can be used along with the SQL MAX() to find the maximum value of a column over each group. The SQL HAVING CLAUSE is reserved for aggregate function. The usage of WHERE clause along with SQL MAX() have also described in this page. The SQL IN OPERATOR which checks a value within a set of


How to select rows which have max and min of count?, This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions. WITH s AS (SELECT MyGroup,  SQL COUNT ( ) with group by and order by . In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT() function. The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group.