Kirk,

This query doesn't make much sense and will probably perform bad as may also not return the correct results.

Do you have ID field in the table and what is the GROUP field you need to group by to get the last transaction? Also, are you using SQL 2000 or greater?

Please take a moment to read through the solutions presented in the first blog.

>Naomi,
>
>Thank you here is what I ended up with for my final solution.
>
>

>SELECT pwh.pathwID,
>	   pwh.TransactionDT,
>	   pwh.patientid,
>	pwh.Height,
>	pwh.HeightMeasureType,
>	pwh.Weight,
>	pwh.WeightMeasureType,
>	pwh.InputSource,
>	pwh.UserID,
>	pwh.NoteID,
>	pwh.BMI,MeasureStatus,
>	pwh.Meetingnumber
>FROM dbo.co_Pat_HeightWeightHistory pwh
>WHERE patientID=@PatientID
>AND pwh.pathwID=(Select top 1 pathwID from co_Pat_HeightWeightHistory p2 
>		WHERE p2.PatientID=@PatientID 
>		AND convert(varchar(10),p2.transactiondt,101)=CONVERT(varchar(10),pwh.transactiondt,101)
>	    ORDER BY TransactionDT DESC)
>ORDER BY pathwid Desc
>
>

>
>>>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