DENSE-RANK to rank the department with most employees

Question

I have a table called employees with follow column:

Field	Type	NULL	Key
employee_id	int	NO	PRI
first_name	varchar(20)	YES
last_name	varchar(25)	NO
email	varchar(100)	NO
phone_number	varchar(20)	YES
hire_date	date	NO
job_id	int	NO	MUL
salary	decimal(8,2)	NO
manager_id	int	YES	MUL
department_id	int	YES	MUL

I want to rank the department with most employees, here was my query:

SELECT department_id, COUNT(employee_id) no_employee, 
    DENSE_RANK() 
    OVER (PARTITION BY department_id 
    ORDER BY COUNT(employee_id) DESC ) Rank_ 
FROM employees 
GROUP BY department_id;

However, the result gave back rank all the departments "1", no matter how much employees they had, like below:

department_id	no_employee	Rank_
1	1	1
2	2	1
3	6	1

At first, I did not add the GROUP BY operator in the query, but then the system showed error of non-aggragate column.

Can someone help me with this?

I expect the result will be like this (the data may not be correct)

department_id	no_employee	Rank_
1	10	1
2	9	2
3	8	3

Answer 1

Remove the partition by clause and rank

SELECT department_id, COUNT(employee_id) no_employee, 
    RANK() OVER (ORDER BY COUNT(employee_id) DESC ) Rank_ 
FROM employees 
GROUP BY department_id;

https://dbfiddle.uk/d227UwOW