query to find the second most common word in a table (oracle sql)-CodePudding

So I have a table employees as shown below

ID | name | department
---|------|-----------
1  | john | home
2  | alex | home
3  | ryan | tech

I'm trying to group these by the department number and have the count displayed. But I am trying to select the second most common, which in this case it should return (tech 1). Any help on how to approach this is appreciated.

Edit:

By only using MINUS, I'm still not familiar with LIMIT when searching around online.

CodePudding user response：

We can use COUNT along with DENSE_RANK:

WITH cte AS (
    SELECT department, COUNT(*) AS cnt,
           DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM yourTable
    GROUP BY department
)

SELECT department, cnt
FROM cte
WHERE rnk = 2;

As of Oracle 12c, you might find the following limit query satisfactory:

SELECT department, COUNT(*) AS cnt
FROM yourTable
GROUP BY department
ORDER BY COUNT(*) DESC 
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

But this limit approach does not handle well the scenario where e.g. there might be 2 or more departments ties for first place. DENSE_RANK does a better job of handling such edge cases.