sqloracle-databasetop-n

How do I select top rows in SQL

I have a code that outputs Department Names and their number of workers.

SELECT department_name,COUNT (employee_id) FROM EMPLOYEES  
JOIN DEPARTMENTS  
ON employees.department_id=departments.department_id
GROUP BY department_name 
ORDER BY count(employee_id) DESC

Output

I want to select the first three department names that have the largest number of workers. . When I WHERE, I get three rows, but the output is not true.

SELECT department_name,COUNT (employee_id) FROM EMPLOYEES  
JOIN DEPARTMENTS  
ON employees.department_id=departments.department_id
WHERE rownum <= 4
GROUP BY department_name 
ORDER BY count(employee_id) DESC

Output

What can you recommend to me about this issue? Also, I will use this query in a PL/SQL block. THANKS!

Solution

  • That rownum clause happens before the sort. You can either use

    fetch first 3 rows only

    after the order by clause, if using Oracle 12c or above, or with rownum use a subquery -

    select * from (
    your query here
) 
where rownum < 4;