CODEWITHSUNDEEP
t-sql
JJ.
JJ.

Reputation: 9950

How do I select the max date from a union of two tables?

SELECT TOP 1 * FROM 
(
    select max(updatedtimestamp) from db1.dbo.policyrepresentationcache with(nolock)
    UNION
    select max(updatedtimestamp) from db2.dbo.policyrepresentationcache with(nolock)
    ) 
ORDER BY updatedtimestamp

I'm running into syntax errors here and im not sure how to set it up.

Upvotes: 1

Views: 7981

Answers (2)

Kumar_2002
Kumar_2002

Reputation: 604

SELECT TOP 1 Temp.* FROM 
(
    select max(updatedtimestamp) from db1.dbo.policyrepresentationcache with(nolock)
    UNION
    select max(updatedtimestamp) from db2.dbo.policyrepresentationcache with(nolock)
    ) AS Temp
ORDER BY Temp.updatedtimestamp

Upvotes: 0

Max Barfuss
Max Barfuss

Reputation: 1482

You're just missing an alias for the table produced by the union. I've aliased it as a below.

SELECT TOP 1 a.updatedtimestamp FROM
(
     select max(updatedtimestamp) as updatedtimestamp  
     from db1.dbo.policyrepresentationcache with(nolock)
     UNION
     select max(updatedtimestamp) 
     from db2.dbo.policyrepresentationcache with(nolock)
) a
ORDER BY a.updatedtimestamp

Upvotes: 4

Related Questions