Jim Cullen Jim Cullen - 1 month ago 15
SQL Question

Select max from calculated column alongside other columns

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.

Answer

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,

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