Russian Soyuz Rocket Russian Soyuz Rocket - 2 months ago 9
SQL Question

Select value with maximum number of entries - Oracle Database

This is what I have:

A table

PATENT
with a column
ASSIGNEE
. I want to find the
ASSIGNEE
with maximum number of entries in the table.

And this is what I am trying to do:

SELECT ASSIGNEE
FROM (
SELECT ASSIGNEE, count(*) num_assignee
FROM PATENT
GROUP BY ASSIGNEE
ORDER BY num_assignee DESC
)
WHERE ROWNUM <= 1


This works great for only one maximum, however, in the case of a tie, the other
ASSIGNEE
is not shown. How to resolve this?

Answer

You may try this:

SELECT assignee
FROM (
    SELECT assignee, 
           count(*) num_assignee,
           MAX(count(*)) OVER () max_num_assignee
    FROM patent
    GROUP BY assignee
    )
WHERE num_assignee = max_num_assignee;