Оператор Rownum возвращает другую строку, чем без него

Question

Итак, у меня есть этот оператор выбора, который возвращает номер отдела, который имеет наименьшее количество людей, работающих как КЛЕРКС, однако он возвращает два отдела из-за данных в базе данных. Когда я добавляю rownum = 1, это дает мне совершенно другой номер отдела, в котором больше всего CLERKS, и я понятия не имею, почему это так. Помощь оценена

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);

Я попытался использовать rownum в главном операторе select и в операторе sub select, но с тем же результатом. Я даже использовал order by, и он все еще давал тот же результат.

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;

Вот то же самое утверждение с rownum и заказом.

Answer 1

Ваша проблема вызвана тем, что where пункт применяется до order by,

Вы можете обойти проблему, сначала отсортировав, а затем применив 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;

Замечания:

Эта проблема специфична для Oracle. MS SQL Server TOP и MySQL LIMIT оба применяются после order by пункт.

Заметка 2:

В Oracle Database 12c (12.1) есть новая функция для выбора строк от k до k + m, offset k rows fetch next m rows only, Я бы рекомендовал использовать его вместо решения выше. Спасибо Лалиту Кумару Б за указание на это.

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

Но что, если есть два (или более) отдела с одинаковым номером? Не волнуйтесь, есть вариант, который возвращает все связи:

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

После другого ответа @Klas:

В Oracle 12c вы можете использовать новую функцию ограничения строк Top-N.

Например,

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