asked on

How can I select the most recent date entry?

Hi, I'm using sql 2005 express
I would like to select one entry from dbo.DailyStepsGoals based on the studyid and ds.DateGoalIsSet (DateTime) is the most recent. How can I put that in Tsql? Thank you.

select p.StudyId, d.LastName, p.DateOfFirstVisit, ds.TargetNumber as StepTargetNumber, 
from dbo.Patients p inner join dbo.Doctors d on p.DoctorId = d.DoctorId
left join dbo.DailyStepsGoals ds on p.StudyId = ds.StudyId and ds.DateGoalIsSet

Open in new window

JoeNuvo

Is it this one?

SELECT TOP 1 p.StudyId, d.LastName, p.DateOfFirstVisit, ds.TargetNumber as StepTargetNumber
from dbo.Patients p 
inner join dbo.Doctors d on p.DoctorId = d.DoctorId
left join dbo.DailyStepsGoals ds on p.StudyId = ds.StudyId
ORDER BY p.StudyId DESC, ds.DateGoalIsSet DESC

Open in new window

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lapucca

ASKER

Joe, sorry, I have a couple of this type selection in this query fro the dg table I also need the most recent DateGoalSet column. I don't think select Top 1 will select the most recent from both dg and ds table? Patient to both ds and dg is one to many relationship., Thank you.

select p.StudyId, d.LastName, p.DateOfFirstVisit, ds.TargetNumber as StepTargetNumber,
from dbo.Patients p inner join dbo.Doctors d on p.DoctorId = d.DoctorId
left join dbo.DailyStepsGoals ds on p.StudyId = ds.StudyId and ds.DateGoalIsSet
left join dbo.DietGoals dg on p.StudyId = dg.StudId and dg.DateGoalIsSet

lapucca

ASKER

Thank you.