Кеер() вместо Over() - незнал...

The Green Nun
Дата: 08.08.2005 18:53:26
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 /

---------- ---------- ----------
10 1300 7934
20 800 7369
30 950 7900

Что-то в документации не встречал этот Кеер. Не подскажите где в документации написано про этот Кеер?
Дата: 08.08.2005 18:56:56
В SQL Reference
The Green Nun
Дата: 08.08.2005 19:01:06
В SQL Reference

Ну и где там?
Andrew Max
Дата: 08.08.2005 19:08:12
На самом деле, функции называются FIRST и LAST.
Например, здесь.
Дата: 08.08.2005 19:25:49
не там ищете посмотрите напр