Posted by Ed Murphy on 05/04/07 02:02

hharry wrote:

> 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

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

[Back to original message]