Re: SQL help needed

> 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

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch