Re: SQL count/max query

From: Ranga Chakravarthi <ranga_at_removethis.cfl.rr.com>
Date: Sat, 16 Mar 2002 20:22:18 GMT
Message-ID: <_PNk8.156008$Dl4.17890000@typhoon.tampabay.rr.com>

Try this:
SELECT studentname, numexams
FROM (

      SELECT studentname, COUNT(studentname) NUMEXAMS,
             RANK() OVER (ORDER BY COUNT(studentname)) rk
       FROM exams, studentcourse
       WHERE exams.papername=studentcourse.papername
       GROUP BY studentname
     )

WHERE rk = 1

STUDENTNAME NUMEXAMS

Angela          1
Elle            1
Gillian         1
Nigel           1

"Monty" <mmontreaux_at_hotmail.com> wrote in message news:6284dd3.0203160921.2cd89bd6_at_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 - 14:22:18 CST