Rownum statement returns different row than without it

Question

Alright so I have this select statement that returns the department number which has the lowest number of people working as CLERKS, however it returns two departments because of the data in the database. When I add rownum=1 it gives me a completely different department number which has the most number of CLERKS and I have no idea why it does so. Help appreciated

select deptno from emp where job='CLERK' group by deptno 
having count(job)=(select min(count(job)) from emp where job='CLERK'group by deptno);

I've tried using rownum in the main select statement and in the sub select statement but the same result.. I even used order by and it still produced the same result.

select deptno from emp where rownum=1 and job='CLERK' group by deptno 
having count(job)=(select min(count(job)) from emp where job='CLERK'group by deptno) order by deptno;

Here is the same statement with the rownum and the order by.

Answer 1

Your problem is caused by the fact that the where clause is applied before order by.

You can get around the problem by sorting first and then applying rownum:

select * from (
    select deptno from emp 
     where job='CLERK' 
     group by deptno 
    having count(job)=(select min(count(job)) from emp where job='CLERK'group by deptno) 
     order by deptno) 
where rownum=1;

Note:

This problem is Oracle specific. MS SQL Server TOP and MySQL LIMIT are both applied after the order by clause.

Note 2:

In Oracle Database 12c (12.1), there is a new feature for selecting rows k through k+m, offset k rows fetch next m rows only. I would recommend using it instead of the solution above. Thanks to Lalit Kumar B for pointing it out.

select deptno from emp 
 where job='CLERK' 
 group by deptno 
having count(job)=(select min(count(job)) from emp where job='CLERK'group by deptno) 
order by deptno
fetch next 1 rows only

But what if there are two (or more) department with the same number? Don't worry, there is a variant that returns all ties:

select deptno from emp 
 where job='CLERK' 
 group by deptno 
having count(job)=(select min(count(job)) from emp where job='CLERK'group by deptno) 
order by deptno
fetch next 1 rows with ties

Answer 2

Following the other answer by @Klas:

In Oracle 12c, you could use the new Top-N row-limiting feature.

For example,

SQL> SELECT empno, sal FROM emp ORDER BY sal DESC;

     EMPNO        SAL
---------- ----------
      7839       5000
      7902       3000
      7788       3000
      7566       2975
      7698       2850
      7782       2450
      7499       1600
      7844       1500
      7934       1300
      7521       1250
      7654       1250
      7876       1100
      7900        950
      7369        800

14 rows selected.

SQL> SELECT empno, sal
  2  FROM emp
  3  ORDER BY sal DESC
  4  FETCH FIRST 5 ROWS ONLY;

     EMPNO        SAL
---------- ----------
      7839       5000
      7788       3000
      7902       3000
      7566       2975
      7698       2850