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

[rski]

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

maybe like that

select max(column) from (select count(*) as column from buys group by v_id) t;

[poopsy]

maybe like that

select max(column) from (select count(*) as column from buys group by v_id) t;

thx a lot it works...but can u help me some more plzz...i need to get the corresponding v_id where the count is maximum..
i tried doing this

select t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t;

bt i get the error
ERROR: column "t.vid" must appear in the GROUP BY clause or be used in an aggregate function
i tried using top 1 bt i dnt knw how to use it..
i did this

select TOP 1 t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t group by t.vid;

bt i get a syntax error near 1..

plzz help

[rski]

thx a lot it works...but can u help me some more plzz...i need to get the corresponding v_id where the count is maximum..
i tried doing this

select t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t;

bt i get the error
ERROR: column "t.vid" must appear in the GROUP BY clause or be used in an aggregate function
i tried using top 1 bt i dnt knw how to use it..
i did this

select TOP 1 t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t group by t.vid;

bt i get a syntax error near 1..

plzz help

I don't think there is TOP option in postgres, I guess you mean LIMIT clause.
Here is the solution, probably not the best one but I'm to tired to look for better one

select vid from (select vid ,count(*) as k from buys group by vid)as t where k=(select max(k) from (select count(*) as k from buys group by vid) l);

[Manjunatha A M]

use limit

select t.vid,max(c) from (select vid,count(*) as c from buys group by vid) t group by vid limit 1;