>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

Take a look at
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

One of the solutions:

SELECT pathwID,Height,HeightMeasureType,Weight,WeightMeasureType,InputSource,TransactionDT,
              UserID,NoteID,BMI,MeasureStatus,Meetingnumber
FROM dbo.co_Pat_HeightWeightHistory H
WHERE patientID=@PatientID
and H.ID = (select top 1 ID from dbo.co_Pat_HeightWeightHistory H1 where H1.pathwID = H.PathwID order by TransactionDT DESC)
ORDER BY PathwID Desc