Re: MAX(COUNT(*))?

On Thu, 30 Jan 2003 17:49:34 +0000, Clairwil wrote:

> From the table
>
> Computer (IdComputer, model, IdDealer, DealerAddress)
>
> I need to find which dealer has sold most computers;
> I can count how many computers they have sold each one, I can order them,
> but I can't find only the bestseller, the maximum value in COUNT(*) column!!
>
> SELECT IdDealer, COUNT(*) FROM Computer
> GROUP BY IdDealer
>
> ORDER BY IdDealer, desc;
>
> Can someone help me?

for the sake of it, here is another one:

select iddealer, count(1)
from computer
group by iddealer
having count(1) = (

	select max(y) from (
		select iddealer as x, count(1) as y from computer group by iddealer
	) z

the db2 "with" construct is real handy for these kind of queries

with calc(iddelaer, cnt) as (
 select iddealer, count(1) from computer group by iddealer ) select * from calc where cnt = (select max(cnt) from calc

HTH
/Lennart Received on Thu Jan 30 2003 - 21:05:00 CET