Help with query

[mbaddar]

Hi,

I need to write two different queries. The first one will find the latest (most recent date) in one table for a particular patient. The same patients can have multiple entries in the table with different dates. I need to find the most recent date.

Then, I need to write another query to find the date that is the second most recent in the same table for the same patient. Does anyone know how I can do this?

Thanks for any help,
MB

 

[tlbroadbent]

Find the latest date.

Select PatientID, Max(date) As MostRecent
From tbl Group By PatientID

Find the date before that.

Select a.PatientID, Max(a.date) As 2ndMostRecent
From tbl As a
Where date<
(Select Max(date)
From tbl As b
Where b.PatientID=a.PatientID
Group By PatientID)
Group By a.PatientID

Terry

;-) USER, n.: The word computer professionals use when they mean &quot;idiot.&quot; -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Watermelon]

Hi,

Thanks, that makes sense. I'll definitely try it.

Thanks,
MB