query difficulties

[Guest_imported]

Hi,

I have a problem with a query... I want to know how I can do to have THE 'name' that occur the most often in a table for example with the number of time it occur...

My solution was :

select name, max(count(name))
from cie
group by name;


but I get the error :

ERROR at line 1:
ORA-00937: not a single-group group function

If someone know how I can do to solve my problem...

Thanks

 

[Guest_imported]

I will like to think that a simple such as
select name, count(name)
from cie
group by name;
will do the trip.
It will give you the name as well as how many times that name occurs.
sokeh

 

[rcurva]

This will get all names with the most no. of count:

SELECT NAME, COUNT(*)
FROM CIE
HAVING COUNT(*) =
(select max(cnt)
from
(select name, count(*) cnt
from CIE
group by name)
)
GROUP BY NAME;

I am sure there is someone out there with better sql than this one.

 

[carp]

Try this one:
SELECT name
FROM (SELECT name, count(*)
FROM CIE
GROUP BY name
ORDER BY count(*) DESC)
WHERE rownum < 2;