BlakeB BlakeB - 1 month ago 16
SQL Question

SQL - Retrieve Associated Primary Key and Value of MAX(value) of Query

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

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