How to get the maximum value from 2 tables

Question

Using Sql Server

I want to get the maximum value from two tables

Table 1

ID Total 101 100 102 600 ..... 

Table2

 ID Total 101 300 102 400 .... 

I want to get the maximum value from 2 tables according to id

Expected Result

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

How to make a request

Query Request Help

Answer 1

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

Answer 2

 ; 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 ; 

Answer 3

Another option worth considering may 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 indexed in two tables, perhaps this may give a better plan (untested)