Query to get the "MAX COUNT"

Question

Guys I have the following query:

select count(*) AS num_items
       from desc_look
       group by dept

This query returns the number of items in each department. However I can't get ONLY the department with the higher number of items.

I've been trying to use this to retrieve the name and id of the department with most items

select dept, desc_up
from desc_look
where (select count(*) AS num_items
       from desc_look
       group by dept)

However I keep getting an error ORA-00936 and I don't know why. I know I can't user MAX(COUNT(*)) but is there a way to workaround this?

Does the `desc_up` column always contain the department name that matches `dept_id` of the same row, i.e. both the department name and id are store in the `desc_look` table? — Mick Mnemonic, Mar 30 '15 at 18:52

score 4 · Accepted Answer · edited May 23 '17 at 12:12

4

select * from
(
    select count(*) AS num_items
    from desc_look
    group by dept
    order by count(*) desc
) tmp
WHERE ROWNUM = 1;

Also have a look on howto limit the records in Oracle.

edited May 23 '17 at 12:12

Community

1
1

answered Mar 30 '15 at 17:12

juergen d

186,950
30
261
325

This answer does not return the department name and id which the OP requested. – Mick Mnemonic Mar 31 '15 at 09:14

Mick Mnemonic · Answer 2 · 2015-03-30T21:15:37.367

This version is basically the same as juergen's, but using an analytic function instead of an aggregate (GROUP BY) for counting:

SELECT t.dept, t.desc_up FROM
  (SELECT dept, desc_up,
   COUNT(*) over (partition BY dept) dept_count
   FROM desc_look
   ORDER BY dept_count DESC
  ) t
WHERE rownum = 1

If you're on Oracle 12, the inline view is not needed because you can use the row-limiting clause (FETCH FIRST ...):

SELECT dept, desc_up,
  COUNT(*) over (partition BY dept) dept_count
FROM desc_look
ORDER BY dept_count DESC
FETCH FIRST 1 ROW ONLY

score 0 · Answer 3 · edited Mar 30 '15 at 21:18

0

i think this may help you

select count(*) AS num_items
       from desc_look
       group by dept
       order by count(*) desc
       limit 1

edited Mar 30 '15 at 21:18

Allan

16,316
4
49
63

answered Mar 30 '15 at 17:38

sandesh jadhav

129
6

1

I don't think one can use `LIMIT` in Oracle. – David Faber Mar 30 '15 at 20:20

score 0 · Answer 4 · answered Mar 30 '15 at 20:22

0

You might try something like this:

SELECT dept, num_items FROM (
    SELECT dept, COUNT(*) AS num_items
         , ROW_NUMBER() OVER ( ORDER BY COUNT(*) DESC ) AS rn
      FROM desc_look
     GROUP BY dept
) WHERE rn = 1;

answered Mar 30 '15 at 20:22

David Faber

11,549
2
25
40

Query to get the "MAX COUNT"

4 Answers4