Select arbitrary single value for GROUP BY: What’s the fastest option

group byoracleoracle-18cquery-performance

I have a query that I use to indicate locations in a map where there are overlapping points:

select
    min(objectid) as min_objectid,
    longitudex,
    latitudey,
    count(1) as count,
    min(shape) as min_shape
from
    workorders
group by
    longitudex,
    latitudey
having
    count(1) > 1

In the mapping software that I use, I need to include columns like objectid and shape. For those columns, it doesn't matter which of the grouped-by rows the values come from, just as long as there is a value.

Presently, I'm using min() to get an arbitrary value for those columns. However, I don't know if that's the fastest option since finding the minimum value would require calculation — and I wonder if that time spent is unnecessary.

What is the fastest option for getting an arbitrary/single value for GROUP BY in an Oracle query?

Best Answer

Your query does return the duplicate locations, but does not return the individual points (work orders) with the same location.

This returns those locations again (same as your query, just reformatted in a more compact notation):

select longitudex, latitudey, count(*)
from workorders
group by longitudex, latitudey
having count(*) > 1;

Then this returns the individual work orders that share the same location:

select objectid, longitudex, latitudey, shape
from workorders
where (longitudex, latitudey) in (
  select longitudex, latitudey
  from workorders
  group by longitudex, latitudey
  having count(*) > 1
);

That obviously only works if the geographical coordinates are exactly the same for work orders at the same location, down to all decimals. If not then you need to use spatial operators to compare the locations. Those are available with databases like Oracle (out of the box) or PostgreSQL (with the PostGis extension).

Related Question