Incluir columnas no agrupadas en Oracle en una vista

Una petición típica es ver el salario mínimo para cada departamento; por lo que nos interesa poder incluir columnas no agrupadas en Oracle en una vista.

Cuyo código es trivial

SQL> select deptno, min(sal)
  2  from emp
  3  group by deptno;

pero si a la petición se añade que se quiere ver el número del empleado que tiene ese salario la cosa se dificulta.

SQL> select deptno, empno, min(sal)
  2  from emp
  3  group by deptno;

ORA-00979: not a GROUP BY expression

No se puede poner una columna que no este agrupada.

En el pasado , esto significaba sub-selects y vista en linea pero ahora se puede hacer de una forma más sencilla:

SQL> select deptno, min(sal),

min(empno)

  2

KEEP ( dense_rank FIRST order by sal) empno

  3  from emp
  4  group by deptno
  5  /

    DEPTNO   MIN(SAL)      EMPNO
---------- ---------- ----------
        10       1300       7934
        20        800       7369
        30        950       7900

Esto funciona bien como consulta, pero no sirve para hacer una vista con estos datos ya que dice que la función KEEP no puede ser usada en una vista.

La solución pasa por usar la siguiente consulta:

SQL> select ename,deptno,sal
  2  from (select ename,deptno,sal,
  3  row_number() over (partition by deptno
  4  order by sal desc,empno) r from emp)
  5  where r=1;

La solución con KEEP es más rápida como consulta, pero como se ha comentado no puede ser usada en una vista.