OraFAQ Forum: SQL & PL/SQL » How to display only one row with max no. of count?

Home » SQL & PL/SQL » SQL & PL/SQL » How to display only one row with max no. of count? (Oracle 11g database)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to display only one row with max no. of count? [message #682601]

Sun, 01 November 2020 09:55

SumuHadi
Messages: 2
Registered: October 2020

Junior Member

Can anyone help me out with this query? I want to display only one row with max no. of employees.

select department_id, count(first_name), (select max(count(first_name))from employees
group by department_id) max_no_of_emp from employees
group by department_id;

Sample Report -
100 6 45
30 6 45
null 1 45
90 3 45
20 2 45
70 1 45
110 2 45
50 45 45
80 34 45
40 1 45
60 5 45
10 1 45

Report message to a moderator

Re: How to display only one row with max no. of count? [message #682602 is a reply to message #682601]

Sun, 01 November 2020 10:09

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Quote:

I want to display only one row with max no. of employees.

Quote:

Sample Report

your sentence and specification are not complete.
There are inconsistencies between the first sentence (one row)and the sample report (several rows).
So what are the correct specification and the correct result?

Report message to a moderator

Re: How to display only one row with max no. of count? [message #682603 is a reply to message #682601]

Sun, 01 November 2020 13:05

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

This solution will read table hr.employees twice:

select  department_id, count(first_name)
  from  hr.employees
  group by department_id
  having count(first_name) = (
                              select  max(count(first_name))
                                from  hr.employees
                                group by department_id
                             )
/

DEPARTMENT_ID COUNT(FIRST_NAME)
------------- -----------------
           50                45

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2525186777

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     1 |    10 |     4  (25)| 00:00:01 |
|*  1 |  FILTER              |           |       |       |            |          |
|   2 |   HASH GROUP BY      |           |     1 |    10 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | EMPLOYEES |   107 |  1070 |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE     |           |     1 |    10 |     4  (25)| 00:00:01 |
|   5 |    SORT GROUP BY     |           |     1 |    10 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| EMPLOYEES |   107 |  1070 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT("FIRST_NAME")= (SELECT MAX(COUNT("FIRST_NAME")) FROM
              "HR"."EMPLOYEES" "EMPLOYEES" GROUP BY "DEPARTMENT_ID"))

19 rows selected.

SQL>

This one only one time:

with t as (
           select  department_id,
                   count(first_name) cnt,
                   max(count(first_name)) over() max_count
             from  hr.employees
             group by department_id
          )
select  department_id,
        max_count
  from  t
  where cnt = max_count
/

DEPARTMENT_ID  MAX_COUNT
------------- ----------
           50         45


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2505388549

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |    11 |   429 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                |           |    11 |   429 |     4  (25)| 00:00:01 |
|   2 |   WINDOW BUFFER      |           |    11 |   110 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |   110 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |  1070 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CNT"="MAX_COUNT")

16 rows selected.

SQL>

And this solution will read table once and also will stop ranking after determining rows with dense rank of 1:

with t as (
           select  department_id,
                   count(first_name) cnt,
                   dense_rank() over(order by count(first_name) desc) flag
             from  hr.employees
             group by department_id
          )
select  department_id,
        cnt max_cmt
  from  t
  where flag = 1
/

DEPARTMENT_ID    MAX_CMT
------------- ----------
           50         45

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2946314347

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    11 |   429 |     5  (40)| 00:00:01 |
|*  1 |  VIEW                    |           |    11 |   429 |     5  (40)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |    11 |   110 |     5  (40)| 00:00:01 |
|   3 |    HASH GROUP BY         |           |    11 |   110 |     5  (40)| 00:00:01 |
|   4 |     TABLE ACCESS FULL    | EMPLOYEES |   107 |  1070 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG"=1)
   2 - filter(DENSE_RANK() OVER ( ORDER BY COUNT("FIRST_NAME") DESC )<=1)

17 rows selected.

SQL>

SY.

Report message to a moderator