Only Need One Record Per Newest Date.

Hey all,

I just discovered this cool forum. I am fairly new to T-SQL, so please bear with me.

Here is my problem. I am trying to return 1 record for each distinct MachineName, based on the most current CreateTime.

I have tried a bunch of different things, but the follwing query seems to get me close, except for all of the records, per MachineName, that aren't the newest CreateTime. Can anyone offer a suggestion I may be missing?

Here is my query:

SELECT
rcv.Name AS MachineName, r.CreateTime, rpv.Path + rpv.Name AS ResourcePool

FROM
dbo.Result_View AS r WITH (NOLOCK) INNER JOIN
dbo.ResourceConfiguration_View AS rcv WITH (NOLOCK) ON r.ResourceConfigurationId = rcv.Id INNER JOIN
dbo.Resource_View AS rv WITH (NOLOCK) ON rcv.ResourceId = rv.Id INNER JOIN
dbo.ResourcePool_View AS rpv WITH (NOLOCK) ON rv.ResourcePoolId = rpv.Id

WHERE
(rpv.Name NOT LIKE 'Archive')
AND
(r.CreateTime > '2/1/2008')
AND
(r.CreateTime = (SELECT TOP (1) r.CreateTime FROM dbo.Result_View))

ORDER BY MachineName


I have added the '2/1/2008' filter, so as not to hammer the DB too hard. It goes back for some time.

Thanks in advance,

Allen