max value

[FengShui1998]

Hello,

I have four tables, A, B, C, D and all have the same field called COST. How can I find the maximum value for COST from all four tables? Thanks for your help in advance.

fengshui1998

 

[cLFlaVA]

This will get what you want:

[tt]SELECT max(A.COST) as Max_Cost_A
, max(B.COST) as Max_Cost_B
, max(C.COST) as Max_Cost_C
, max(D.COST) as Max_Cost_D
FROM A, B, C, D[/tt]

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[FengShui1998]

cLFlaVA,

Thanks for your reply but sorry for not clarifying my question. I need one single value for cost and the second field is where userid = 'thisuser'

Thanks again,
fengshui1998

 

[swampBoogie]

select max(m) from (
SELECT max(COST) as M from a union
select max(COST) from b union
select max(COST) from c union 
select max(COST) from d) dt

 

[cLFlaVA]

SELECT max(Cost)
  FROM (SELECT max(COST) as Cost
          FROM A
         WHERE userid = 'thisuser' UNION
        SELECT max(COST) as Cost
          FROM B
         WHERE userid = 'thisuser' UNION
        SELECT max(COST) as Cost
          FROM C
         WHERE userid = 'thisuser' UNION
        SELECT max(COST) as Cost
          FROM D
         WHERE userid = 'thisuser')

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[FengShui1998]

Thanks to cLFlaVA and swampBoogie!!!

fengshui1998