Selecting only earliest record - Query Help

[alexdski]

I'm no SQL whizz yet but I'm learning hard, and need to get some information from our DB rather urgently so have resorted to this fantastic forum, only I can't find what I'm looking for.

Basically I'm selecting a whole load of entries that have a (admission)date field after 2001, but I only want to return the Earliest (admission) for each (patients number).

Here is the script I have created to select all the data, but how can I limit the results to just the earliest (admission date) for each (patient).


SELECT
Admission_Year, Admission_Month, Age_On_Admission, [Length of stay(continuing)], [Patient's Number], [Cons epis seq no], Sex, [Main Primary Pas Diag], [Date of Death], [Epi duration], [OP Code1], [Admission date], [Date of Death] - [Admission date] AS [days before death],[Intended Management]
FROM dbo.Admissions
WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)


I would really appreciate it if anyone can help with this, I'm sorry I can't really contribute to this forum as an SQL expert as .net is really my forte and I usually spend my time contributing to the asp.net forums.

[Rawhide]

First off, you need to find the earliest admission date for each patient. You do that by grouping on their unique patient number and selecting the Minimum admission date:

SELECT [Patient's Number], Min([Admission date]) As FirstAdmitDate
From dbo.Admissions
WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
Group By [Patient's Number]

Once you have that logic worked out, use it as a dynamic table and join it to your existing query.

Select Admission_Year, Admission_Month, Age_On_Admission, [Length of stay(continuing)], A.[Patient's Number], [Cons epis seq no], Sex, [Main Primary Pas Diag], [Date of Death], [Epi duration], [OP Code1], [Admission date], [Date of Death] - [Admission date] AS [days before death],[Intended Management]
FROM dbo.Admissions A
Inner Join (SELECT [Patient's Number], Min([Admission date]) As FirstAdmitDate
From dbo.Admissions
WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
Group By [Patient's Number]) As dT On dT.[Patient's Number] = A.[Patient's Number] And dT.FirstAdmitDate = A.[Admission date]
WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
And Exists (Select 1 From dbo.Admissions
WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
Group By [Patient's Number]