How to get max value from 2 tables

Using Sql Server

I want to get 开发者_运维问答max value from two table

Table1

ID Total

101 100
102 600
.....

Table2

ID Total

101 300
102 400
....

I want to get a max value from 2 table according to the id

Expected Output

ID Total

101 300 (max value in table2)
102 600 (max value in table1)
....
...

How to make a Query

Need Query Help

SELECT
   ID, MAX(Total)
FROM
    (
    SELECT ID, Total FROM Table1
    UNION ALL
    SELECT ID, Total FROM Table2
    ) foo
GROUP BY
   ID

; with
q_00 as (
select id, Total from Tbl_1
union all
select id, Total from Tbl_2
)
select id, max(Total)
from q_00
group by id
order by id ;

One other option worth considering might be

WITH T(ID, Total)
     AS (SELECT ID,
                MAX(Total)
         FROM   Table1
         GROUP  BY ID
         UNION ALL
         SELECT ID,
                MAX(Total)
         FROM   Table2
         GROUP  BY ID)
SELECT ID,
       MAX(Total) AS Total
FROM   T
GROUP  BY ID  

If ID,Total is indexed in the two tables possibly this might give a better plan (untested)