Re: SQL help needed

"Daniel Morgan" <damorgan_at_x.washington.edu> schreef in bericht news:1077658619.450961_at_yasure...
> Rene Nyffenegger wrote:
>
> >>I am doing a course in which we are now using SQL-plus. One of the
queries
> >>that has to be made I can not get solved (very annoying).
> >>
> >>It handles about a database that contains presidents of the United
States,
> >>together with e.g. marriages and hobbies of these presidents. The
database
> >>contains amongst others the following two tables: president and
pres_hobby.
> >>President has pres_name and party as attributes (pres_name is primairy
key),
> >>and pres_hobby has pres_name and hobby as attributes (combination of
those
> >>two attributes is primairy key). Pres_hobby.pres_name is foreign key for
> >>president.pres_name.
> >>
> >>
> >>
> >>The query that has to be designed:
> >>
> >>Give the name of the president, together with party and number of
hobbies of
> >>this presidents, that have the largest number of hobbies of all
presidents
> >>within there party.
> >>
> >>
> >>
> >>I have been trying (see below) but I get only one president, the one
with
> >>the most hobbies of all, and it should give presidents per party with
most
> >>hobbies.
> >>
> >>
> >>
> >>Who can help me?
> >>
> >>
> >>
> >>SELECT p.party, p.pres_name, count(*)
> >>FROM president p, pres_hobby h
> >>WHERE p.pres_name = h.pres_name
> >>GROUP BY p.party, p.pres_name
> >> HAVING count(*) = (SELECT max(count(*))
> >> FROM president p2, pres_hobby h2
> >> WHERE p2.pres_name = h2.pres_name
> >> GROUP BY p2.pres_name);
> >
> >
> > You might want to try analytical functions:
> >
> > drop table pres_hobby;
> > drop table president;
> >
> > create table president (
> > pres_name varchar2(40) primary key,
> > party varchar2(40)
> > );
> >
> > create table pres_hobby(
> > pres_name references president not null,
> > name varchar2(40)
> > );
> >
> > insert into president values('Allan', 'REP');
> > insert into president values('Basil', 'REP');
> > insert into president values('Cats', 'DEM');
> > insert into president values('De More', 'DEM');
> > insert into president values('Eric', 'REP');
> > insert into president values('Frot', 'DEM');
> >
> > insert into pres_hobby values('Allan', 'TV');
> > insert into pres_hobby values('Allan', 'Ping Pong');
> >
> > insert into pres_hobby values('Basil', 'Golf');
> > insert into pres_hobby values('Basil', 'TV');
> > insert into pres_hobby values('Basil', 'Reading');
> >
> > insert into pres_hobby values('Cats', 'Eating');
> >
> > insert into pres_hobby values('De More', 'Wine');
> > insert into pres_hobby values('De More', 'Computer');
> > insert into pres_hobby values('De More', 'Arts');
> > insert into pres_hobby values('De More', 'TV');
> >
> > insert into pres_hobby values('Eric', 'Reading');
> >
> > insert into pres_hobby values('Frot', 'Writing');
> >
> >
> > select party, pres_name, cnt from (
> > select
> > p.party,
> > p.pres_name,
> > count(*) cnt,
> > row_number() over (partition by p.party order by count(*) desc) r
> > from
> > president p join pres_hobby h on p.pres_name = h.pres_name
> > group by
> > p.party, p.pres_name
> > )
> > where r = 1;
> >
> > hth
> > Rene
>
> Great ... but students rarely learn from having solutions handed
> to them. It would have been preferable if you'd provided a hint
> or two and let the OP work through it.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>

This solution is too difficult for me. I assume it can be done easier, because we have not discussed anything like "partition" or "row_number..." It has to be solved using max(count(*))
Can anyone tell we what is wrong in my solution? Received on Tue Feb 24 2004 - 16:07:17 CST