Re: MAX(COUNT(*))?

"Clairwil" <clairwil_at_hotmail.com> wrote in message news:OAd_9.63989$YG2.1793074_at_twister1.libero.it...
> 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?

SELECT Sales1.iddealer, Sales1.sold
FROM (SELECT iddealer, COUNT(*) AS sold

             FROM Computer
             GROUP BY iddealer) AS Sales1
            LEFT OUTER JOIN
            (SELECT iddealer, COUNT(*) AS sold
             FROM Computer
             GROUP BY iddealer) AS Sales2
            ON Sales2.sold > Sales1.sold

Regards,
jag Received on Thu Jan 30 2003 - 20:13:11 CET