Compare SQL groups against eachother

Question

How can one filter a grouped resultset for only those groups that meet some criterion compared against the other groups? For example, only those groups that have the maximum number of constituent records?

I had thought that a subquery as follows should do the trick:

SELECT * FROM (
    SELECT   *, COUNT(*) AS Records
    FROM     T
    GROUP BY X
) t HAVING Records = MAX(Records);

However the addition of the final HAVING clause results in an empty recordset... what's going on?

Answer 1

In MySQL (Which I assume you are using since you have posted SELECT *, COUNT(*) FROM T GROUP BY X Which would fail in all RDBMS that I know of). You can use:

SELECT  T.*
FROM    T
        INNER JOIN
        (   SELECT  X, COUNT(*) AS Records
            FROM    T
            GROUP BY X
            ORDER BY Records DESC
            LIMIT 1
        ) T2
            ON T2.X = T.X

This has been tested in MySQL and removes the implicit grouping/aggregation.

If you can use windowed functions and one of TOP/LIMIT with Ties or Common Table expressions it becomes even shorter:

Windowed function + CTE: (MS SQL-Server & PostgreSQL Tested)

WITH CTE AS
(   SELECT  *, COUNT(*) OVER(PARTITION BY X) AS Records
    FROM    T
)
SELECT  *
FROM    CTE
WHERE   Records = (SELECT MAX(Records) FROM CTE)

Windowed Function with TOP (MS SQL-Server Tested)

SELECT  TOP 1 WITH TIES *
FROM    (   SELECT  *, COUNT(*) OVER(PARTITION BY X) [Records]
            FROM    T
        )
ORDER BY Records DESC

Lastly, I have never used oracle so apolgies for not adding a solution that works on oracle...

---

EDIT

My Solution for MySQL did not take into account ties, and my suggestion for a solution to this kind of steps on the toes of what you have said you want to avoid (duplicate subqueries) so I am not sure I can help after all, however just in case it is preferable here is a version that will work as required on your fiddle:

SELECT  T.*
FROM    T
        INNER JOIN
        (   SELECT  X
            FROM    T
            GROUP BY X
            HAVING  COUNT(*) = 
                    (   SELECT  COUNT(*) AS Records
                        FROM    T
                        GROUP BY X
                        ORDER BY Records DESC
                        LIMIT 1
                    )
        ) T2
            ON T2.X = T.X