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
    OPENQUERY(LinkedServer, 'SELECT [TimeStamp]
 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.

  • Is there a typo? How can tags and name be different in the results if the query says t1.TAGS = t2.Name? – Dave Johnson Mar 6 '13 at 13:05
  • Table DogTags has a single column named TAGS – Greenhorn Mar 6 '13 at 13:17
  • But 'ROLF' != 'REX'. I'm a little confused. – Dave Johnson Mar 6 '13 at 13:21
  • OH yes you are right... I will Edit it. Thanks. Typo – Greenhorn Mar 6 '13 at 13:24

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:

FROM [MyDatabase].[dbo].[DogTags] t1
    '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

EDIT: Needed to add AS TimeStamp in the subquery.

EDIT: Needed to specify the database everytime in OPENQUERY.

| improve this answer | |
  • Unfortunately doesn't works, I get the following error OLE DB provider "SQLNCLI10" for linked server "LinkedServer" returned message "Deferred prepare could not be completed.". – Greenhorn Mar 6 '13 at 13:40
  • I will give it a try the other way around, I will first select the data from the linked server and then the local one. Thanks for your time :) – Greenhorn Mar 6 '13 at 13:42
  • I looked up that error, and it could be that I need to explicitly use the DB name in the OPENQUERY. I might need to use [LinkedServer].[dbo].[PETS]. Is the DB on the other server actually called LinkedServer? – Dave Johnson Mar 6 '13 at 13:46
  • no it is called ROPSA – Greenhorn Mar 6 '13 at 13:52
  • Probably need to use [ROPSA].[PETS] instead of [LinkedServer].[PETS] then. – Dave Johnson Mar 6 '13 at 13:54

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

Select 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 
| improve this answer | |
    SELECT * FROM [MyDatabase].[dbo].[DogTags] t1
     FROM [CustomerDB].[PETS] t3
     SELECT [Name] AS JailTag, MAX(TimeStamp) AS MaxDate
     FROM [ROPSA].[CustomerDB].[PETS]
     GROUP BY [Name]
     ON t3.[Name] = t4.[JailTag] AND t3.TimeStamp = t4.MaxDate ')t2
    ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT
| improve this answer | |
  • Ah, so I think I just needed to use [CustomerDB] every time I referenced a table in OPENQUERY. I edited my answer. – Dave Johnson Mar 7 '13 at 19:13
  • if there is 2 rows with the same timestamp and tag, there still can be duplicates. – Jānis Mar 8 '13 at 12:50
  • yes, you are right, in the case of having 2 rows with same values that can occur, but I can use SELECT DISTINCT and will sort it out. – Greenhorn Mar 8 '13 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.