Jim Cullen - 6 months ago 47

SQL 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.

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;
```