Using sub-queries in SQL to find max(count())

Question

I am using a program called SQLfire to code and I'm not entirely sure what version we are using but I'm told it needs to work with SQL Server 2008.

Here is what I'm trying to do:

select CustomerNum, max(count(CustomerNum))
from Rentals
group by CustomerNum

I am aware that the question of how to properly implement max(count()) has already been answered multiple times, however, I have not found any way to solve it that works with SQLfire. So, I tried solving it using a correlated sub-query like so:

select CustomerNum, count(CustomerNum)
from Rentals R
group by CustomerNum
having count(CustomerNum) =
    (select max(CustomerNum)
    from Rentals
    having count(CustomerNum) = count(R.CustomerNum))

but I found out that I have absolutely no idea what I'm doing. Is there a way to solve this problem using basic commands and sub-queries?

For reference, we are only using the column CustomerNum (1000,1001,1002 etc) in table Rentals. I am trying to find the customer whose CustomerNum appears the most times in table Rentals. I am thinking around using sub-queries to first count the number of times each customernum appears in the table, then find the customernum with the highest count.

Answer 1

You don't need a correlated subquery for what you are doing. Here is one way based on your query:

select CustomerNum, count(CustomerNum)
from Rentals R
group by CustomerNum
having count(CustomerNum) = (select max(cnt)
                             from (select CustomerNum, count(CustomerNum) as cnt
                                   from Rentals
                                   group by CustomerNum
                                  ) rc
                            );

I would be inclined to move the subquery to the from clause and use subqueries:

select rc.*
from (select CustomerNum, count(CustomerNum) as cnt
      from Rentals R
      group by CustomerNum
     ) rc join
     (select max(cnt) as maxcnt
      from (select CustomerNum, count(CustomerNum) as cnt
            from Rentals
            group by CustomerNum
           ) rc
     ) m
     on rc.cnt = m.maxcnt;

These are standard SQL and should work in both systems. In practice, I'd probably find a way to use top or row_number() on SQL Server 2008.