Select MaxDate where hh:mm:ss makes the difference

Question

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.

Answer 1

In the OPENQUERY, you could get the max timestamp for all the dogs at once, and then join to the original PETS table to get the other columns, like so:

SELECT *
FROM [MyDatabase].[dbo].[DogTags] t1
JOIN OPENQUERY(ROPSA,
    'SELECT [TimeStamp], [Name], [Owner], [Address]
     FROM [CustomerDB].[PETS] t3
     JOIN (SELECT [Name], MAX(TimeStamp) AS TimeStamp
           FROM [CustomerDB].[PETS]
           GROUP BY [Name]) t4
     ON t3.[Name]=t4.[Name] AND t3.TimeStamp=t4.TimeStamp') t2
ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

EDIT: Needed to add AS TimeStamp in the subquery.

EDIT: Needed to specify the database everytime in OPENQUERY.

Answer 2

Can try do like this (to get only one row per [Name] with maximum [TimeStamp] value):

Select PETS.* 
From PETS
    Inner Join
        (
            Select ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [TimeStamp] DESC, MyID Desc) Rn, MyID
            From PETS
        ) ps on PETS.MyID = ps.MyID and ps.Rn = 1 

Answer 3

    SELECT * FROM [MyDatabase].[dbo].[DogTags] t1
    LEFT JOIN
    OPENQUERY(ROPSA, 'SELECT *
     FROM [CustomerDB].[PETS] t3
     INNER JOIN 
     (
     SELECT [Name] AS JailTag, MAX(TimeStamp) AS MaxDate
     FROM [ROPSA].[CustomerDB].[PETS]
     GROUP BY [Name]
     )t4
     ON t3.[Name] = t4.[JailTag] AND t3.TimeStamp = t4.MaxDate ')t2
    ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT