>I do have an ID field, that is the PatHWID, and it is basically grouping on the TransactionDT field. This is SQL 2000, so there is not just a date field, the TransactionDT field is a datetime field and I need to exclude the time portion. I'm assuming the performance issue you're concerned about is with the convert in the where clause both on the right and left side of the =.
>
>Since I don't have just a date field to play with, this was the only way to get the grouping basically. Right now, the table has 138,000 records and will continue to grow over time, so that may be an issue. Right now it return in the sub-second range. I have tried it with 25 different PatientID keys and so for they are all checking out with the correct data.
>
>Kirk
>

Are you grouping for the TransactionDate field (without time portion) and taking the latest datetime for that date?

I didn't realize that the TransactionDate was the field to group by, I thought you group by other field(s).

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