Task

Find the top   N   salaries in each department,   where   N   is provided as a parameter.

Use this data as a formatted internal data structure (adapt it to your language-native idioms, rather than parse at runtime), or identify your external data source:

Employee Name,Employee ID,Salary,Department
Tyler Bennett,E10297,32000,D101
John Rappl,E21437,47000,D050
George Woltman,E00127,53500,D101
Adam Smith,E63535,18000,D202
Claire Buckman,E39876,27800,D202
David McClellan,E04242,41500,D101
Rich Holcomb,E01234,49500,D202
Nathan Adams,E41298,21900,D050
Richard Potter,E43128,15900,D101
David Motsinger,E27002,19250,D202
Tim Sampair,E03033,27000,D101
Kim Arlich,E10001,57000,D190
Timothy Grove,E16398,29900,D190



CREATE OR REPLACE PROCEDURE "Top rank per group"(TOP_N IN PLS_INTEGER DEFAULT 3) AS
  CURSOR CSR_EMP(TOP_N PLS_INTEGER) IS
    SELECT CASE LINE
             WHEN 10 THEN
              'Tot.' || LPAD(POPULATION, 2) || ' Employees in ' || TIE_COUNT ||
              ' deps.Avg salary:' || TO_CHAR(SALARY, '99990.99')
             WHEN 30 THEN
              '-'
             WHEN 50 THEN
              'Department: ' || DEPT_ID || ', pop: ' || POPULATION ||
              '. Avg Salary: ' || TO_CHAR(SALARY, '99990.99')
             WHEN 70 THEN
              LPAD('Employee ID', 14) || LPAD('Employee name', 20) ||
              LPAD('Salary', 9) || 'Rank'
             WHEN 90 THEN
              LPAD('+', 14, '-') || LPAD('+', 20, '-') || LPAD('+', 9, '-') ||
              LPAD('+', 4, '-')
             ELSE
              LPAD(' ', 8) || LPAD(EMP_ID, 6) || LPAD(EMP_NAME, 20) ||
              TO_CHAR(SALARY, '99990.99') ||
              LPAD(CASE WHEN TIE_COUNT = 1 THEN  ' ' ELSE 'T' END || RANK, 4)
           END "Top rank per group"
      FROM (SELECT 10 LINE
                  ,NULL EMP_ID
                  ,NULL EMP_NAME
                  ,' ' DEPT_ID
                  ,AVG(SALARY) SALARY
                  ,0 RANK
                  ,COUNT(DISTINCT DEPT_ID) TIE_COUNT
                  ,COUNT(*) POPULATION
              FROM EMP
            UNION ALL
            SELECT 30      LINE
                  ,NULL    EMP_ID
                  ,NULL    EMP_NAME
                  ,DEPT_ID
                  ,0       SALARY
                  ,0       RANK
                  ,0       TIE_COUNT
                  ,0       POPULATION
              FROM EMP
             GROUP BY DEPT_ID
            UNION ALL
            SELECT 50 LINE
                  ,NULL EMP_ID
                  ,NULL EMP_NAME
                  ,DEPT_ID
                  ,AVG(SALARY) SALARY
                  ,0 RANK
                  ,0 TIE_COUNT
                  ,COUNT(*) POPULATION
              FROM EMP
             GROUP BY DEPT_ID
            UNION ALL
            SELECT 70      LINE
                  ,NULL    EMP_ID
                  ,NULL    EMP_NAME
                  ,DEPT_ID
                  ,0       SALARY
                  ,0       RANK
                  ,0       TIE_COUNT
                  ,0       POPULATION
              FROM EMP
             GROUP BY DEPT_ID
            UNION ALL
            SELECT 90      LINE
                  ,NULL    EMP_ID
                  ,NULL    EMP_NAME
                  ,DEPT_ID
                  ,0       SALARY
                  ,0       RANK
                  ,0       TIE_COUNT
                  ,0       POPULATION
              FROM EMP
             GROUP BY DEPT_ID
            UNION ALL
            SELECT 110 LINE
                  ,EMP_ID
                  ,EMP_NAME
                  ,DEPT_ID
                  ,SALARY
                  ,(SELECT COUNT(DISTINCT EMP4.SALARY)
                      FROM EMP EMP4
                     WHERE EMP4.DEPT_ID = EMP3.DEPT_ID
                       AND EMP4.SALARY >= EMP3.SALARY) RANK
                  ,(SELECT COUNT(*)
                      FROM EMP EMP2
                     WHERE EMP2.DEPT_ID = EMP3.DEPT_ID
                       AND EMP2.SALARY = EMP3.SALARY) TIE_COUNT
                  ,0 POPULATION
              FROM EMP EMP3
             WHERE TOP_N >= -- Here is the meat, Correlated subquery
                   (SELECT COUNT(DISTINCT EMP4.SALARY)
                      FROM EMP EMP4
                     WHERE EMP4.DEPT_ID = EMP3.DEPT_ID
                       AND EMP4.SALARY >= EMP3.SALARY))
     ORDER BY DEPT_ID
             ,LINE
             ,SALARY DESC
             ,EMP_ID;
 
  V_EMP CSR_EMP%ROWTYPE;
BEGIN
  FOR V_EMP IN CSR_EMP(TOP_N)
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp."Top rank per group");
  END LOOP;
END;