>Trying to figure out how to return just 1 record for each date in the results. In the attached screen shot, you'll see there are a total of 5 records, one with a TransactionDT of 10-06-2010 and 4 with dates on 10-04-2010. I'd like to return from the query, just the latest record from each date. So in this case it would only return 2 records, 1 for the 6th and 1 for the 4th.
>
>The current query is below, and I'm filtering them out in the application code, but am trying to move the logic into the stored procedure.
>

>SELECT pathwID,Height,HeightMeasureType,Weight,WeightMeasureType,InputSource,TransactionDT,
>              UserID,NoteID,BMI,MeasureStatus,Meetingnumber
>FROM dbo.co_Pat_HeightWeightHistory 
>WHERE patientID=@PatientID
>order by pathwid Desc
>

>
>Thanks for any advice
>
>Kirk

SELECT  t1.*
FROM    dbo.co_Pat_HeightWeightHistory t1
        INNER JOIN ( SELECT FLOOR(CAST(TransactionDT AS float)) AS dGroup,
                            MAX(TransactionDT) AS TransactionDT
                     FROM   dbo.co_Pat_HeightWeightHistory
                     WHERE  patientID = @PatientID
                     GROUP BY FLOOR(CAST(TransactionDT AS float))
                   ) t2 ON t1.TransactionDT = t2.TransactionDT
WHERE   patientID = @PatientID
order by pathwid Desc

Cetin