How to select the maximum date from a table where hh:mm:ss is selected?
My query is the following, and returns the latest date, but if there are different times recorded, the date will be returned twice. What I want is to get the absolute maxDate.
SELECT * FROM [MyDatabase].[dbo].[DogTags] t1
JOIN
OPENQUERY(LinkedServer, 'SELECT [TimeStamp]
,[Name]
,[Owner]
,[Address]
FROM PETS
WHERE TimeStamp IN (SELECT MAX(TimeStamp) FROM [LinkedServer].[PETS]
GROUP BY [Name])')t2
ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT
This query returns the following table:
TAGS|TimeStamp | Name |Owner | Address |
ROLF|2010-03-03 16:59:01.190| ROLF |OLSEN | Juniper St.|
ROLF|2010-03-03 19:40:01.182| ROLF |OLSEN | Juniper St.|
BLAD|2012-05-01 14:35:11.182| BLAD |JOHN | Maple St. |
The records I want is:
TAGS|TimeStamp | Name |Owner | Address |
ROLF|2010-03-03 19:40:01.182| ROLF |OLSEN | Juniper St.|
BLAD|2012-05-01 14:35:11.182| BLAD |JOHN | Maple St. |
Does anyone knows the solution how to filter the records to max time?
Thanks for the help.