SQL Query for MAX value

[dbadmin]

Hi,

I have two tables like below

Table1
------

c1 a1 a2 a3 a4
----------------------------------
aaa 4 3 2 2
bbb 2 1 3 1

Table2
------

p1 a1 a2 a3 a4
----------------------------------
ccc 1 2 3 4

I get two values as input to my query which are the values of columns c1 and p1. I need to write a query which will get the outputs from both tables for those input values and finds the maximum value in column a1. This is what I have done so for. My first query looks like this (query1)

SELECT a1
FROM table2
WHERE c1='aaa'
UNION SELECT a1
FROM table3
WHERE p1='ccc';

My second query (query2)

SELECT max(a1) AS a
FROM query1;

Is there any way I could combine these queries into one query? something like this

select t1.max(a1) from
(SELECT a1
FROM table2
WHERE c1='aaa'
UNION SELECT a1
FROM table3
WHERE p1='ccc') as t1

Thanks,
dbadmin

 

[cmmrfrds]

Are you saying that your example did not work. If so, what was the error.

 

[dbadmin]

Hi,

Thanks for the quick reply. My example did work, but I want to combine them as one query.

Thanks,
dbadmin.

 

[cmmrfrds]

This is 1 query? Do you mean something different?

select t1.max(a1) from
(SELECT a1
FROM table2
WHERE c1='aaa'
UNION SELECT a1
FROM table3
WHERE p1='ccc') as t1

 

[dbadmin]

Ok, I figured out the solution, I don't need an alias for the second query. Thanks!

dbadmin