SQL count/max query

From: Monty <mmontreaux_at_hotmail.com>
Date: 16 Mar 2002 09:21:25 -0800
Message-ID: <6284dd3.0203160921.2cd89bd6@posting.google.com>

The following query returns a list of all students in a database, and the number of exams each are sitting. Is there anyway to modify this query to only return the people that are sitting the fewest number of exams (and exclude all the others)?

The fewest number of exams in this database is 1, but I don't want a solution with a 'HAVING COUNT(studentname)=1' hard-coded/appended to the end-of the query. I would like to use MIN(..) but can't work out the SQL statement syntax.

Thank you in advance
Monty

CREATE TABLE exams(papername VARCHAR2(10)); INSERT INTO exams VALUES ('Physics');
INSERT INTO exams VALUES ('Biology');
INSERT INTO exams VALUES('Math');
INSERT INTO exams VALUES('English');

CREATE TABLE studentcourse(papername VARCHAR2(10), studentname VARCHAR2(10));

INSERT INTO studentcourse VALUES ('Physics','Peter');
INSERT INTO studentcourse VALUES ('Physics','Nigel');
INSERT INTO studentcourse VALUES ('Physics','John');
INSERT INTO studentcourse VALUES ('Biology','Angela');
INSERT INTO studentcourse VALUES ('Biology','Peter');
INSERT INTO studentcourse VALUES('Math','Peter');
INSERT INTO studentcourse VALUES('Math','John');
INSERT INTO studentcourse VALUES('Math','Christine');
INSERT INTO studentcourse VALUES('English','Elle');

INSERT INTO studentcourse VALUES('English','Gillian'); INSERT INTO studentcourse VALUES('English','Christine');

SELECT studentname, COUNT(studentname)
 FROM exams, studentcourse
 WHERE exams.papername=studentcourse.papername  GROUP BY studentname; Received on Sat Mar 16 2002 - 11:21:25 CST