Comparing SQL groups with eachother

Question

How can I filter a grouped set of results only for those groups that meet a certain criterion compared to other groups? For example, only those groups that have the maximum number of component records?

I thought the following subquery should do the trick:

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

However, adding a final HAVING results in an empty entry ... what happens?

Answer 1

For the exact question you are giving, one way to look at it is that you want a group of records where there is no other group that has more records. Therefore, if you say

 SELECT taxid, COUNT(*) as howMany GROUP by taxid 

You get all counties and their number

Then you can consider this expression as a table, making it a subquery and assigning it an alias. Below I assign the two "copies" of the request the names X and Y and request from tax authorities that are no longer in the same table. If there are two with the same number, I would get two or more. Various databases have proprietary syntax, especially TOP and LIMIT, which make this type of query simpler and more understandable.

 SELECT taxid FROM (select taxid, count(*) as HowMany from flats GROUP by taxid) as X WHERE NOT EXISTS ( SELECT * from ( SELECT taxid, count(*) as HowMany FROM flats GROUP by taxid ) AS Y WHERE Y.howmany > X.howmany ) 

Answer 2

In MySQL (which I assume you are using since you sent SELECT *, COUNT(*) FROM T GROUP BY X Which failed in all the RDBMS 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 = TX 

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

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

Window function + CTE: (MS SQL-Server and 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) 

Window 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 

Finally, I never used the oracle, so I did not add a solution that works on oracle ...

---

EDIT

My MySQL solution did not take into account the connection, and my proposal for solving this kind of steps on the fingers of what you said you want to avoid (duplicate subqueries), so I'm not sure I can help after everything, but just in case, the version is preferable here , which will work as needed on your violin:

 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 = TX 

Answer 3

Try the following:

 SELECT * FROM ( SELECT *, MAX(Records) as max_records FROM ( SELECT *, COUNT(*) AS Records FROM T GROUP BY X ) t ) WHERE Records = max_records 

I'm sorry that I can’t verify this request right now.