Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has

same field name calcuated_price has max value is 3000.

Nishith

SELECT MAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECT CASE

WHEN MAX(P.iPlanid) > MAX(AP.iPlanid) THEN MAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

END AS maxvalue

FROM

PLANS P

INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen