BlakeB - 10 months ago 51

SQL Question

I have an SQL problem in regards to selecting a max value in a table/query. I am trying to return the maximum value returned from an aggregate expression as well as the associated primary key of the tuple.

I am able to return 1 tuple displaying only the maximum value when I only include the aggregate function in the select statement but I am unable to return just 1 tuple with BOTH the primary key and the max value of the aggregate expression.

Here is an example of my query:

`SELECT id, MAX(SQRT((POWER((x-(-28)),2) + POWER((y-(151)),2)))) as distance`

FROM table

GROUP BY id;

As you would expect this returns all the id's and the associated value from the function. It doesn't return just 1 tuple with the maximum value and the associated id.

What can I do to fix this?

Answer Source

One way to do this is by ordering by your expression in descending order, and only selecting the first row:

```
select id, distance
from (select id, SQRT((POWER((x-(-28)),2) + POWER((y-(151)),2))) as distance
from table
order by distance desc)
where rownum < 2
```

Starting with Oracle 12c, you can also take advantage of the `fetch first n rows`

syntax to accomplish the same thing:

```
select id, SQRT((POWER((x-(-28)),2) + POWER((y-(151)),2))) as distance
from table
order by distance desc
fetch first row only
```

If more than one row shares the same maximum distance value, you can also conveniently adjust the syntax to support ties:

```
select id, SQRT((POWER((x-(-28)),2) + POWER((y-(151)),2))) as distance
from table
order by distance desc
fetch first row with ties
```