Path: utzoo!attcan!uunet!mcsun!hp4nl!orcenl!bengsig
From: bengsig@oracle.nl (Bjorn Engsig)
Newsgroups: comp.databases
Subject: Re: SQL Question
Keywords: SQL
Message-ID: <1004@nlsun1.oracle.nl>
Date: 5 Oct 90 08:10:11 GMT
References: <391@ulticorp.UUCP>
Reply-To: bengsig@oracle.nl (Bjorn Engsig)
Organization: Oracle Europe, The Netherlands
Lines: 28

Article <391@ulticorp.UUCP> by mikei@ulticorp.UUCP (mikei) says:
|Greetings,
|
|Given a table of patients PID int   WEIGHT int
|where one PID may have numerous weight readings.
|
|How would I select the PID for only those patients whose
|weight was ALWAYS <= 100.

select pid
from patients
group by pid
having max(weight)<=100;

or, if your database engine does not have having, first create a view
giving the max weights of the patients, and then select the one <100 
from it:

create view patmax (pid,weight) as
select pid,max(weight) from patients group by pid;

select pid
from patmax
where weight<=100;
-- 
Bjorn Engsig,	Domain:		bengsig@oracle.nl, bengsig@oracle.com
		Path:		uunet!mcsun!orcenl!bengsig
		From IBM:	auschs!ibmaus!cs.utexas.edu!uunet!oracle!bengsig