Re: Help with simple SQL problem

From: Pavel Polcar <pavel.polcar_at_berit.cz>
Date: 5 May 1998 07:00:22 GMT
Message-ID: <01bd77f3$6fbce2d0$47c3657e@polcar_nt4006>

Hi,
the following should do the trick. In fact, I tested it and it works. The only problem is that there may be more film makers with the maximum number of films (hence the "in" operator). If you want just one of them, add rownum=1 to the outer where condition.

Hope this helps you,:

select maker_name
from film_maker
where filmmaker_id in (

   select filmmaker_id from film
   group by filmmaker_id
   having count(*)= (

           select max(count(*))
           from film
           group by filmmaker_id));

--
Pavel Polcar

steelx_at_hotmail.com wrote in article <6iaduq$3bd$1_at_nnrp1.dejanews.com>...
> I'm a newbie when it comes to databases and SQL. I'm now working with
Oracle
> 7 and am messing around with tables to model a video shop. I've the
> following tables:
>
> FILM(film_no, film_name, filmmaker_id)
> FILM_MAKER(filmmaker_id, maker_name, maker_address)
>
> The filmmaker_id in FILM is a foreign key.
>
> The problem is, how do I find out the film maker that has the most films
with
> the video shop?
>
> Note: I hope that this is the right place for me to ask questions like
this.
> If it's not, please point me out to the appropriate newsgroup to ask.
Also,
> I would really appreciate it if you CC your reply to steelx_at_hotmail.com
>
> Thanks in advance!
>
> El Ti
> steelx_at_hotmail.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
Received on Tue May 05 1998 - 02:00:22 CDT