sql to take maximum number from both the tables?

Question

I need to query the maximum ID from two tables and I need to take the ID of whichever is larger. i am using sqlserver.

Queries:

SELECT MAX(a.ID)
FROM   tableA a

SELECT MAX(b.ID)
FROM   tableB b

If tableA's maximum ID is 20 and tableB's maximum ID is 30 then the UNION of both the tables query should return only 30.

Is it possible to combine both the queries into a single query to return maximum ID?

Answer 1

This is based on what you said, UNION both tables and get the maximum value.

SELECT max(ID)
FROM
(
    select max(ID) ID from tableA
    UNION
    select max(ID) ID from tableB
) s

or

SELECT max(ID)
FROM
(
    select ID from tableA
    UNION
    select ID from tableB
) s

Answer 2

SELECT MAX(id)
FROM (SELECT ID FROM tableA
      UNION
      SELECT ID FROM tableB) AS D