CODEWITHSUNDEEP
sqloracle-databasemaxgreatest-n-per-group
user5104026
user5104026

Reputation: 698

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?

Upvotes: 1

Views: 116

Answers (2)

Christian Delos Reyes
Christian Delos Reyes

Reputation: 28

Try this.

SELECT assignee 
FROM
(
    SELECT TOP 1 assignee, count(*) num_assignee
    FROM patent
    GROUP BY assignee
    ORDER BY num_assignee DESC
) Source

Upvotes: -2

hemalp108
hemalp108

Reputation: 1249

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;

Upvotes: 3

Related Questions