title: Getting department names that have maximum staff count
body: <p>I have 2 tables, one table storing details of staff (columns are staff_id, staff_name, department_id)  and another table storing details of department (columns are department_id, department_name,  department_block_num).  </p>
  
  <p>I need to write a query to display names of department that has maximum staff count order by department_name. It is also given that multiple department can also have staff count same as maximum staff count. Another condition is group function is not allowed. </p>
  
  <p>Here is code</p>
  
  <pre><code>SELECT department_name
    FROM department
   WHERE department_id IN (SELECT department_id
                             FROM (  SELECT department_id, COUNT (*) AS cnt1
                                       FROM staff
                                      WHERE COUNT (*) =
                                               (SELECT cnt
                                                  FROM (  SELECT department_id,
                                                                 COUNT (*) AS cnt
                                                            FROM staff
                                                        GROUP BY department_id
                                                        ORDER BY cnt DESC)
                                                 WHERE ROWNUM = 1)
                                   GROUP BY department_id));
  </code></pre>