Select max from calculated column alongside other columns

Question

I have a table that has columns ID, FIELD1, FIELD2, all of type NUMBER.

I want to find the MAX of a function on FIELD1 and FIELD2, and display that alongside the ID.

I try

SELECT ID, MAX(SQRT(FIELD1 + FIELD2)) AS CALC FROM TABLE;

But it returns ORA-00937: not a single-group group function.

I tried the solutions in this thread, but they have their own errors.

SELECT * FROM ( SELECT ID, SQRT(FIELD1 + FIELD2) AS CALC, RANK() OVER (ORDER BY CALC DESC) AS RANKING FROM TABLE ) WHERE RANKING = 1;

gives the error

ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_CALC'

and so does

SELECT ID, SQRT(FIELD1 + FIELD2) AS CALC FROM TABLE WHERE CALC = ( SELECT MAX(CALC) FROM TABLE );

Using Oracle Database 11g Express Edition Release 11.2.0.2.0.

How can I get this to work? Thanks.

hemalp108 · Accepted Answer · 2016-10-24 09:26:17Z

1

Can you try the below two queries.

SELECT ID,FIELD1,FIELD2,SQRT(FIELD1 + FIELD2) AS CALC FROM TABLE WHERE SQRT(FIELD1 + FIELD2)= (SELECT MAX(SQRT(FIELD1 + FIELD2)) FROM TABLE);

Or, Suggested by Aleksej without using aggregate or group by function.

SELECT * FROM ( SELECT ID, SQRT(FIELD1 + FIELD2) FROM TABLE ORDER BY 2 DESC ) WHERE ROWNUM=1;

Initial query,

SELECT * FROM ( SELECT ID, MAX(SQRT(FIELD1 + FIELD2)) AS CALC FROM TABLE GROUP BY ID ORDER BY 2 DESC ) WHERE ROWNUM=1;

edited Oct 24, 2016 at 9:26

answered Oct 24, 2016 at 8:01

hemalp108

1,2492 gold badges16 silver badges23 bronze badges

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

6 Comments

Jim Cullen Over a year ago

Whoops sorry. I wrote out that second example wrong. It should've been SELECT ID, MAX(SQRT(FIELD1 + FIELD2) AS CALC. But anyway, no, your answer is not what I'm looking for. I want a single result returned, the highest one from the table. The GROUP BY in your example prints out hundreds of results.

Aleksej Over a year ago

@JimCullen - Notice that this queries do different things, for example the first one can return more than one row, the second one can not

Jim Cullen Over a year ago

Will the first one return more than one answer if there are multiple rows tied for highest value of CALC?

hemalp108 Over a year ago

Yes the first query will return the records with the maximum value for CALC, hence if there are multiple records for the max value then all the records will be fetched. The secord query will give only the record even if there are multiple IDs for max value for CALC. Its upto you to decide which one to use for your requirement

Aleksej Over a year ago

@hemalp108 - Are you sure you need the MAX and GROUP in the second query? given that you use an ORDER, isn't aggregation unuseful?

|

Aleksej · Accepted Answer · 2016-10-24 08:46:02Z

If you need a single value, with the maximum sqrt(field1+field2) among all the values of ID, these are two possible ways:

select * from ( select * from yourTable order by sqrt(field1 + field2) desc ) where rownum = 1 select id, field1, field2 from ( select t.*, row_number() over ( order by sqrt(field1 + field2) desc) as rn from yourTable t ) where rn = 1

Notice that if you have more than one ID with the same, maximum value, this will pick one of them randomly.

Collectives™ on Stack Overflow

Select max from calculated column alongside other columns

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related