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 |