-2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Zypher100
  • 43
  • 4
  • Duplicate of https://stackoverflow.com/questions/2491063/how-to-find-the-employee-with-the-second-highest-salary (using the `COUNT` in the order by clause). – MT0 Feb 10 '22 at 10:32

1 Answers1

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • is there another way without the use of DENSE_RANK – Zypher100 Feb 10 '22 at 10:00
  • 1
    If there are multiple top ranked rows then `OFFSET 1 FETCH NEXT 1 ROWS ONLY` will fetch one of the joint-top values and not the second ranked value. If there is only one top-ranked row then the equivalent to `DENSE_RANK` would be `OFFSET 1 FETCH NEXT 1 ROW WITH TIES` (but that still would get the wrong value if there are multiple top-ranked values). – MT0 Feb 10 '22 at 10:09