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); Received on Tue Feb 24 2004 - 11:10:10 CST