0

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?

  • when you don't care why do you put there a function in the first place. – nbk Oct 10 at 23:05
  • 1
    You could try "first", no need to compare. – Gerard H. Pille Oct 11 at 6:24
  • i doubt that you can prove that tfirst is faster than Min , also we had here question with the answer that add over and partition makes it faster – nbk Oct 11 at 9:13
  • @nbk I'm not familiar with "add over and partition". Can you explain? Thanks. – User1973 Oct 11 at 12:16
  • @User1973 see window functions docs.oracle.com/cd/E17952_01/mysql-8.0-en/… – nbk Oct 11 at 14:28
2

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

| improve this answer | |
1
+50

With Oracle 12.1 or later, add this to the end of the query:

fetch first row only

By the way, count(*) is the standard expression across all SQL variants as far as I'm aware.

For your example, that would be:

select
    min(objectid) as min_objectid,
    longitudex,
    latitudey,
    count(*) as count,
    min(shape) as min_shape
from
    workorders
group by
    longitudex,
    latitudey
having
    count(*) > 1
fetch first 1 row only
| improve this answer | |
  • 1
    Right, it's an urban myth that has never been true, that count(1) is faster than count(*) – a_horse_with_no_name Oct 11 at 8:24
  • Thanks. FETCH is new to me. Would you be able to give me a hint as to what the full query would look like? This is what I tried, but I'm obviously not doing it right: i.stack.imgur.com/9L6Kp.png – User1973 Oct 14 at 0:28
  • Added example based on the original question. The version in the screenshot seems to be failing because objectid and shape aren't in the group by or aggregated. – William Robertson Oct 14 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.