"line fusion" in oracle

Question

I am trying to do "line fusion" in Oracle, i.e. I want to have a query that returns lines, and each of these lines has, for each column, values that were originally stored in other lines.

For instance (I am following the documentation), let's say I have an employee table EMP storing the employee number, the number of his department and his salary

How do I create a query that returns as many lines as departments, and that returns as well the max salary in that department?

I want something like this:

 EMPNO     DEPTNO        SAL MAX_VAL_IN_DEPT
---------- ---------- ---------- -----------------
  7934         10       1300              1300
  7369         20        800               800
  7900         30        950               950

This query:

SELECT empno
     , deptno
     , sal
     , FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno ORDER BY sal ASC NULLS LAST) AS first_val_in_dept
FROM   emp;

is the closest I could find, but I would need an extra GROUP BY deptno clause that I cannot add.

Answer 1

This will be faster than the version with a separate sub-select.

Your statement was close, you just need to use max():

select empno,  
       deptno,
       sal,
       max(sal) over (partition by deptno) as MAX_VAL_IN_DEPT
from emp 

Not sure about your "as many lines as departments" statement though. Your sample output clearly shows one "line" per employee, not department.