max count

poopsy

hi all
i have the following query, i need to get the max count from a table

select max(count(*))
from buys
group by v_id

bt i have the error aggregate function calls may not be nested
plzzz help

siva538

Quote:

Originally Posted by poopsy

hi all
i have the following query, i need to get the max count from a table

select max(count(*))
from buys
group by v_id

bt i have the error aggregate function calls may not be nested
plzzz help

Probably you are looking for this !

Expand|Select|Wrap|Line Numbers

 
SELECT TOP 1 * FROM 

(

    SELECT v_id, COUNT(*) AS COUNT

    FROM buys

    GROUP BY v_id

) A

ORDER BY 2 DESC

poopsy

Quote:

Originally Posted by siva538

Probably you are looking for this !

Expand|Select|Wrap|Line Numbers

 
SELECT TOP 1 * FROM 

(

    SELECT v_id, COUNT(*) AS COUNT

    FROM buys

    GROUP BY v_id

) A

ORDER BY 2 DESC

it doesnt work..it gives me syntax error near 1
plzz help

deric

Quote:

Originally Posted by poopsy

hi all
i have the following query, i need to get the max count from a table

select max(count(*))
from buys
group by v_id

bt i have the error aggregate function calls may not be nested
plzzz help

You created another thread with the same content as this thread.. You need to review the posting guidelines of this site again. :D

Let's get back to your original script. You cannot use the aggregate function Count() as a parameter to some other aggregate functions like Max(). Anyhow, because your statement doesn't have Where clause and the count function does not specify a particular column/value to count, it will just output rows with always the same value. So, what particular count in your table are you trying to get?
If you can provide sample rows and your expected output, that would be easy for me to help you.