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

Raphaël Althaus · Accepted Answer · 2014-09-12 11:22:30Z

3

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

edited Sep 12, 2014 at 11:22

answered Sep 12, 2014 at 10:23

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DavidDunham Over a year ago

Great! Much appreciated

DavidDunham Over a year ago

Now how would I add select of another column (let's say the ID column). So I have 4 rows in my table and want the IDs (1,2,3,4) and also the MAX as second column (the MAX in every row)

Raphaël Althaus Over a year ago

@Silvanmidix what's your dbms, by the way ?

DavidDunham Over a year ago

It's Oracle. I am querying with iReport and because the software (iReport) is not very smart I need to put the data together as nicely as possible.

NDJ · Accepted Answer · 2014-09-12 10:24:59Z

1

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

edited Sep 12, 2014 at 10:24

NDJ

5,1941 gold badge20 silver badges28 bronze badges

answered Sep 12, 2014 at 10:24

Ermintar

1,3924 gold badges24 silver badges43 bronze badges

Collectives™ on Stack Overflow

Sql select max from result column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related