Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

3 Answers

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.

share|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
show 4 more comments

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 
share|improve this answer
    
Thanks, I will give it a try. –  Greenhorn Mar 6 '13 at 13:18
add comment
up vote 1 down vote accepted
    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
share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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