Displaying Department Name Having Least Number of Employees in SQL Server

There are multiple methods to display the department name having the least number of employees in SQL Server. Here are some of them:

Method 1: Using subquery and MIN() function

SELECT department_name
FROM departments
WHERE department_id = (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING COUNT(*) = (
    SELECT MIN(emp_count)
    FROM (
      SELECT COUNT(*) AS emp_count
      FROM employees
      GROUP BY department_id
    ) AS counts
  )
);

Explanation: - The subquery (SELECT COUNT(*) AS emp_count FROM employees GROUP BY department_id) returns the number of employees in each department. - The subquery (SELECT MIN(emp_count) FROM ...) AS counts returns the minimum number of employees among all departments. - The outer query selects the department name where the number of employees is equal to the minimum number of employees.

Method 2: Using subquery and RANK() function

SELECT department_name
FROM (
  SELECT department_name, RANK() OVER (ORDER BY COUNT(*) ASC) AS rank
  FROM employees
  JOIN departments ON employees.department_id = departments.department_id
  GROUP BY department_name
) AS ranked
WHERE rank = 1;

Explanation: - The subquery SELECT department_name, RANK() OVER (ORDER BY COUNT(*) ASC) AS rank FROM ... assigns a rank to each department based on the number of employees in ascending order. - The outer query selects the department name where the rank is 1, which corresponds to the department with the least number of employees.

Method 3: Using TOP and ORDER BY clauses

SELECT TOP 1 department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY department_name
ORDER BY COUNT(*) ASC;

Explanation: - The query SELECT TOP 1 department_name FROM ... GROUP BY department_name ORDER BY COUNT(*) ASC selects the department name with the least number of employees by grouping the employees by department and ordering the groups by the number of employees in ascending order, and then selecting the first group.