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.

Does SQLfire have window functions (like `ROW_NUMBER()`) and `OVER` clause? — ypercubeᵀᴹ, Sep 11 '13 at 12:03
Not sure about SQLFire's ability to limit the resultset, so i'll not post as an answer, but something like `select top 1 CustomerNum from Rentals group by CustomerNum order by count(CustomerNum) desc` should give you the customernum with the most records — CodingIntrigue, Sep 11 '13 at 12:05
Ah, as this is for assessment, we are prohibited from using the ORDER BY clause :( which seems awfully futile when it could solve this problem almost instantly. as for ROW_NUMBER, OVER and TOP, they haven't been covered in the lecture content yet so even if they work i doubt we are allowed to use them. unfortunately all i have to go with is correlated sub-queries... but i'll check if there is something i missed that might help. thanks for the quick replies! — Kuro, Sep 11 '13 at 12:10
Just noticed you said *it needs to work with SQL 2008* and *I am using a program called SQLfire*. Are you using [VMware SQLfire](http://www.vmware.com/products/vfabric-sqlfire)? — CodingIntrigue, Sep 11 '13 at 12:13
No, I am using a customized version for QUT students, but it's probably derived from that. I couldn't say. — Kuro, Sep 11 '13 at 12:19

score 1 · Accepted Answer · answered Sep 11 '13 at 12:16

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.

This got the result i needed, cheers! placing sub-queries in the from clause was causing me issues earlier, but there were too many possibilities to test. Thanks! — Kuro, Sep 11 '13 at 12:27

score 0 · Answer 2 · answered Sep 11 '13 at 12:07

0

select r.* 
from Rentals r
right join (select CustomerNum, Max(cnt) from (
   select CustomerNum, Count(CustomerNum) cnt from Rentals Group by CustomerNum) tt) t on r.CustomerNum = t.CustomerNum

answered Sep 11 '13 at 12:07

athabaska

455
3
22

This code is nearly spot on, however I'm not entirely sure how this code would work. I haven't covered the on clause yet. – Kuro Sep 11 '13 at 12:32
"on" clause is to specify joining conditions of table – athabaska Sep 11 '13 at 12:46

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

2 Answers2

Linked