Posted by --CELKO-- on 05/04/07 03:40

It is NOT a table at all; it has NO key and it has an IDENTITY (ugh!)
column. If you had used a relational design, ISO-11179 naming rules
etc. instead of this, would it look like this?

CREATE TABLE Patient_Visits_By_Years
(patient_id INTEGER NOT NULL
REFERENCES Patients (patient_id),
visit_year INTEGER NOT NULL
CHECK (visit_year BEYWEEN 1900 AND 2007),
visit_cnt INTEGER NOT NULL
CHECK (visit_cnt > 0),
PRIMARY KEY (patient_id, visit_year));

>> I need to return a single row for each patient; displaying the maximum number of visits and the year those visits occurred <<

SELECT VY1.patient_id, VY1.visit_year, VY1.visit_cnt
FROM Patient_Visits_By_Year AS VY1
WHERE VY1.visit_cnt
= (SELECT MAX(visits_cnt)
FROM Patient_Visits_By_Year AS VY2
WHERE VY2.patient_id = VY1.patient_id);

Learn the right conventions and ANSI/ISO Standards or you will always
be writing dialect and not SQL.

[Back to original message]