All,

I have the following table:

CREATE TABLE [PATIENT_VISITS_BY_YEAR] (
[ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PATIENT_ID] [int] NOT NULL ,
[YEAR_IN_QUESTION] [int] NOT NULL ,
[NUM_OF_VISITS] [int] NOT NULL
) ON [PRIMARY]
GO

With rows:

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (1, 2000, 10)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (1, 2001, 20)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (2, 2000, 50)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (2, 2001, 25)

I need to return the a single row for each patient, displaying the max
number of visits and the year those visits occurred:

e.g.

PATIENT_ID: 1
YEAR_IN_QUESTION: 2001
NUM_OF_VISITS: 20

PATIENT_ID: 2
YEAR_IN_QUESTION: 2000
NUM_OF_VISITS: 50

Thanks in advance!

hharry wrote:

Here are two ways to do it.

select t1.Patient_ID, t1.Year_in_Question, t1.Num_of_Visits
from Patient_Visits_by_Year t1
where t1.Num_of_Visits = (
select max(Num_of_Visits)
from Patient_Visits_by_Year t2
where t2.Patient_ID = t1.Patient_ID
)

select t1.Patient_ID, t1.Year_in_Question, t1.Num_of_Visits
from Patient_Visits_by_Year t1
left join Patient_Visits_by_Year t2
on t1.Patient_ID = t2.Patient_ID
and t1.Year_in_Question < t2.Year_in_Question
where t2.Year_in_Question is null

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));

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.