oracle dbms max returning all values

Question

I am trying to run the following query on an Oracle express edition database.

SELECT SIGHTING_ID, MAX(DISTANCE)
FROM
(
    SELECT SIGHTING_ID,
          SQRT(POWER(LATITUDE + 28, 2) + POWER(LONGITUDE - 151, 2)) AS DISTANCE
    FROM SIGHTINGS
)
GROUP BY SIGHTING_ID;

The purpose of the query is to return The SIGHTING_ID and DISTANCE for the sighting with the maximum distance from the co-ords (-28, 151)

The description for the table SIGHTINGS is as follows

 desc SIGHTINGS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SIGHTING_ID                               NOT NULL NUMBER
 SPOTTER_ID                                         NUMBER
 BIRD_ID                                            NUMBER
 LATITUDE                                           NUMBER
 LONGITUDE                                          NUMBER
 SIGHTING_DATE                                      DATE
 DESCRIPTION                                        VARCHAR2(255)

When I run the query instead of selecting the maximum distance it selects and shows the entire list of sightings and their respective distances. SIGHTINGS has a large amount of tuples but below is a small list of the current output

 SIGHTING_ID MAX(DISTANCE)
 ----------- -------------
 264172    2.01556444
 264174    2.34029913
 264180    2.87647354
 264198    .637887137
 264205    2.08568933
 264211    .232594067
 264215    2.34104677
 264221           .75
 264224    .148660687
 264235    .684470598

My question is how do I make it so that the MAX aggregate function works so that it returns just the maximum distance and the respect SIGHTING_ID?

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Use Oracle's rownum :

SELECT * FROM 
    (SELECT SIGHTING_ID, SQRT(POWER(LATITUDE + 28, 2) + POWER(LONGITUDE - 151, 2)) AS DISTANCE
     FROM SIGHTINGS
     ORDER BY distance DESC)
WHERE ROWNUM = 1

The reason you're getting a record for each SIGHTING_ID is because you are grouping by it. A group by clause means -> give me 1 record for each combination of the columns mentioned inside the clause .

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 13, 2016 at 11:50

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

14 Comments

hemalp108 Over a year ago

If there are two records with the maximum distance in that case it would skip one of the records.

sagi Over a year ago

And how do you know that's not what he wants? @hemalp108

hemalp108 Over a year ago

I've no doubt in your answer, let him decide what he wants. I am just asking it for my curiosity.

Lee Phillips Over a year ago

Thank you so much, worked like a charm after removing the extra bracket

user5683823 Over a year ago

@FrankSchmitt - You are right again. Not enough coffee yet!

|

sagi · Accepted Answer · 2016-10-13 11:54:27Z

1

In Oracle 12c+, you can use ANSI standard functionality:

SELECT SIGHTING_ID,
      SQRT(POWER(LATITUDE + 28, 2) + POWER(LONGITUDE - 151, 2)) AS DISTANCE
FROM SIGHTINGS
ORDER BY distance DESC
FETCH FIRST 1 ROW ONLY;

In earlier versions, @sagi's solution is probably the best way.

edited Oct 13, 2016 at 11:54

sagi

40.6k7 gold badges66 silver badges87 bronze badges

answered Oct 13, 2016 at 11:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

user5683823 Over a year ago

The correct solution for this kind of problems is the first/last aggregate function (as I demonstrate in my answer). docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm

Frank Schmitt Over a year ago

@mathguy Your wording is misleading. Your approach is one of several correct solutions - Gordon Linoff's is another one, and sagi's yet another.

user5683823 Over a year ago

@FrankSchmitt - You are right, I should have said "best", not "correct." For Oracle 12c, Gordon's solution is equally good; I was disagreeing with his assessment for earlier versions. I would edit my comments but on SO that is not possible.

Gordon Linoff Over a year ago

@mathguy . . . I prefer Sagi's solution, because it more easily generalizes to multiple columns (or all columns within a row).

user5683823 · Accepted Answer · 2016-10-13 12:54:11Z

0

select min(sighting_id) keep (dense_rank first order by distance) as sighting_id,
       min(distance)                                              as distance
from sightings;

This will find the row or rows with the shortest distance, and from those rows (and those only), if there are ties (the shortest distance is achieved by two or more sighting id's), it selects the one with the smallest sighting_id. If instead you want the one with the greatest latitude (say), still from those with the shortest distance, change order by distance to order by distance, latitude desc. Other combinations are treated the same way.

answered Oct 13, 2016 at 12:54

user5683823

Collectives™ on Stack Overflow

oracle dbms max returning all values

3 Answers 3

14 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related