Include non-grouped columns


A typical request from a customer...

"Show me lowest salary for each department..."

for which we can trivially code:

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

then the customer adds "...and I need to see employee number as well"

which is a little more difficult.

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

ORA-00979: not a GROUP BY expression

The reason for this is the request is somewhat poorly worded. What is probably meant is that the customer would like to the employee number for the person that actually has the lowest salary in that department.

In the past, this means sub-selects / inline views. But with some new analytic options in 9i, this becomes a trivial

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