Posted by hharry on 05/04/07 00:02

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!

[Back to original message]