Re: SQL Question

dorian_at_wam.umd.edu (Dorian Winterfeld) writes:

> I want to create a view in a ProC program that joins two tables.
> There are two fields that are relevent to my question, PATIENT, which
> is a unique ID similar to employee number, and VISIT. VISIT is a date
> field in the other table and for each PATIENT record there can be
> several VISIT records.
> Here's the problem:
> In my view I want each record to contain the PATIENT field and
> THE MOST RECENT VISIT field for that patient.

You could try this:

CREATE	VIEW PATIENT_VISIT (PATIENT, VISIT) AS
SELECT	DISTINCT P.PATIENT, V.VISIT
FROM	VISIT_TABLE V, PATIENT_TABLE P
WHERE	P.PATIENT = V.PATIENT	/* Restrict to visits for this patient only */
AND	V.VISIT =		/* Restrict to patient's latest visit */
	(
	SELECT	MAX(VISIT)
	FROM	VISIT_TABLE
	WHERE	PATIENT = P.PATIENT
	) ;

I included the DISTINCT keyword in case a patient may have two or more visits with the same date. If that's not the case it would be better (i.e., would in most cases result in faster queries via the view) if removed.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany