Unanswered: Trying to get multiiple max records and having problems...
I have a table that has multiple records for each reference number. I am trying to select
the max dateUpdated record for each reference number. The problem I am having is that my max
dateUpdated is different for each reference number and the other fields within the record may
have unique data so the select statement may pull more than 1 record for the same reference number because the whole record is not distinct... This statement works but only if the data is the same in all records except referencenum and dateupdated.....
SELECT DISTINCTROW [ReferenceNum], [Description], [Type], max([DateUpdated]) AS LastUpdated
GROUP BY [ReferenceNum], [Description], [Type]
WITH OWNERACCESS OPTION;
Someone please HELP..... All I want to pull is the whole record that has a the last updated
from ProjectTestTable xx
= ( select max([DateUpdated])
where [ReferenceNum] = xx.[ReferenceNum] )
the subquery selects the latest date for each ReferenceNum, and that's the row that is selected by the outer query