Home » SQL & PL/SQL » SQL & PL/SQL » How to display only one row with max no. of count? (Oracle 11g database)
How to display only one row with max no. of count? [message #682601] Sun, 01 November 2020 09:55 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
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?

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
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.
icon14.gif  Re: How to display only one row with max no. of count? [message #682606 is a reply to message #682603] Mon, 02 November 2020 08:37 Go to previous messageGo to next message
SumuHadi
Messages: 2
Registered: October 2020
Junior Member
Thanks, Yakobson!

It really works.
Re: How to display only one row with max no. of count? [message #682607 is a reply to message #682606] Mon, 02 November 2020 09:32 Go to previous message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what did you learn?

Previous Topic: Improve Performance of Insert and Update on same table
Next Topic: Joining multiple tables with case
Goto Forum:
  


Current Time: Sun May 16 14:33:27 CDT 2021