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

lapuccaAsked:
Who is Participating?
 
Pratima PharandeCommented:
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
where ds.DateGoalIsSet = (select Max(d2.DateGoalIsSet)
from DailyStepsGoals d2 where d2.StudyId= p.StudyId)
0
 
JoeNuvoCommented:
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

0
 
lapuccaAuthor Commented:
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
0
 
lapuccaAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.