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.
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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading