Sql select max from result column

Question

I am trying to select the MAX from another query result which returns only one column with values.

The query which gives me the column with all the values from which I want to only select the max is:

SELECT perfy as perf
FROM aasv_chart5
UNION
SELECT perfcumu as perf
FROM aasv_chart5

This returns

PERF
-2.9
1.4
12.5
14

now I want to select the Max value from that. I tried something like..

select MAX(SELECT perfy as perf
    FROM aasv_chart5
    UNION
    SELECT perfcumu as perf
    FROM aasv_chart5) FROM aasv_chart5

Answer 1

use your first query as a subquery, but use MAX on a field, not on the query

SELECT MAX(perf) from 
  (SELECT perfy as perf
   FROM aasv_chart5
   UNION
   SELECT perfcumu as perf
   FROM aasv_chart5) s

depending on your dbms, your could also do

select max(greatest(perfy, perdcumu))
from aasv_chart5

or if greatest in not there

select max(case when perfy > perdcumu then perfy else perdcumu end)
from aasv_chart5

EDIT

from your comment, you can do

select id, 
  (select max(greatest(perfy, perdcumu))
  from aasv_chart5) as maxVal
from aasv_chart5

or

select id, s.maxval
from aasv_chart5
cross join  (select max(greatest(perfy, perdcumu)) as maxval
  from aasv_chart5) s

Answer 2

SELECT max(abc.perf)
FROM
(SELECT perfy as perf
    FROM aasv_chart5
    UNION
    SELECT perfcumu as perf
    FROM aasv_chart5) as abc