0

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.

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

3 Answers 3

3

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.

9
  • 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, 2013 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, 2013 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? Mar 6, 2013 at 13:46
  • no it is called ROPSA
    – Greenhorn
    Mar 6, 2013 at 13:52
  • Probably need to use [ROPSA].[PETS] instead of [LinkedServer].[PETS] then. Mar 6, 2013 at 13:54
1

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 
0
1
    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
3
  • Ah, so I think I just needed to use [CustomerDB] every time I referenced a table in OPENQUERY. I edited my answer. Mar 7, 2013 at 19:13
  • if there is 2 rows with the same timestamp and tag, there still can be duplicates.
    – Jānis
    Mar 8, 2013 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, 2013 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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