CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11972

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

Upvotes: 9

Views: 11479

Answers (3)

Martin Smith
Martin Smith

Reputation: 453047

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)

Upvotes: 1

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 2

gbn
gbn

Reputation: 432210

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

Upvotes: 12

Related Questions