Nesting the SQL COUNT() function in MAX(COUNT())

EXPERT RESPONSE FROM: Rudy Limeback

QUESTION POSED ON: 23 June 2006

I am trying to run this query and I get an error:

SQL> select
2 * from member m
3 where m.username IN (select fg.username from filmography fg,
4 (select f.username,max(count(awards)) from filmography f
5 group by username,awards
6 having max(count(awards))
7 >0));
having max(count(awards))
*
ERROR at line 6:
ORA-00935: group function is nested too deeply

Please help me.

EXPERT RESPONSE

Okay, the error message is saying that you cannot nest functions like that.

It looks like all you want is the member with the most awards. Here's how I would do this in Oracle:

select username
, othercolumns
from member
where username in
( select username
from ( select username
from filmography
group
by username
order
by count(awards) desc
) as t
where rownum = 1
)

WHERE ROWNUM=1 is used to obtain the row which has the largest COUNT(AWARDS) in the innermost subquery.

I have seen many people struggled with this type of question at university now I have found a solution that works.