CODEWITHSUNDEEP
sqloracle-database
Cyreaver
Cyreaver

Reputation: 17

Oracle return highest value of count

edited for clarity

So I'm using Oracle SQL Developer and have a fairly basic database set up. I'm trying to count the total number of duplicates of two merged columns and return the field with the highest amount.

I'm using Oracle IDE 19.2.1.

This is what I have so far:

select
    customer_x_coord, customer_y_coord, count(*) as total_in_grid

from 
    customers
        
group by
    customer_x_coord,
    customer_y_coord

order by total_in_grid desc;

This returns the highest number of duplicates as the top row of the output. I figured I could just put 'fetch first 1 rows only;' at the bottom of the query:

select
    customer_x_coord, customer_y_coord, count(*) as total_in_grid

from 
    customers
        
group by
    customer_x_coord,
    customer_y_coord

order by total_in_grid desc

fetch first 1 rows only;

This caused the following error: 'SQL command not properly ended'. Could someone explain what is causing this?

Fetch first 1 row only will only work if there is a definite highest if there are multiple with the same value it would be inaccurate to just bring back the first row. Can someone point me in a direction for a better solution?

Upvotes: 1

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Perhaps the semicolon. Try:

. . . 
order by total_in_grid desc
fetch first 1 row only

If that doesn't work, then perhaps you are using an older version of Oracle. You can use a subquery with the order by included:

select t.*
from (<your query here>
     ) t
where rownum = 1;

Upvotes: 1

Related Questions