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

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.

Answer

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;