SQLTeam.com | Weblogs | Forums

How to get the maximum Date


#1

I have a date with multiple values and I want the latest date ex : if it is 3/2/2018 and 4/5/2018
I just want 4/5/2018
I am using this statement but not successful, so how do we get it to work

Select MAX(dateHistory) AS UpdateDate, rowID as UserID, value AS OldValue FROM [cox].[caV2Utility].[fieldHistory]
where idHistory in (SELECT id FROM [coxs].[caV2Utility].[history]
WHERE idHistoryObject = 141 And rowID in ('1529111')
And description = 'House1819')
GROUP By rowID, dateHistory

Getting this error
Msg 8120, Level 16, State 1, Line 1
Column 'cox.caV2Utility.fieldHistory.value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


#2
SELECT dateHistory AS UpdateDate, rowID as UserID, value AS OldValue
FROM (
Select *, ROW_NUMBER() OVER(ORDER BY dateHistory DESC) AS row_num
FROM [cox].[caV2Utility].[fieldHistory]
where idHistory in (SELECT id FROM [coxs].[caV2Utility].[history]
WHERE idHistoryObject = 141 And rowID in ('1529111')
And description = 'House1819')
) AS query1
WHERE row_num = 1

#3

Thank you but this again returns 2 values, I need only one value which is the latest value for that particular User


#4

Never mind, it was my mistake and now it works


#5

Thank you so much