2 Replies Latest reply: Sep 2, 2011 1:50 PM by 654684 RSS

    Top 2 records per employee ID

    654684
      I am trying to retrieve the top two salary records per employee ID to show their current salary and one previous. The salary table is a transaction type table showing all salary changes per employee.

      For example:

      Employee_ID
      Salary_Effective_Date
      Salary_Amount

      We are using Oracle 10G, SQLPlus. We normally select the max(salary_effective_date) to get their current salary_amt. Is there anyway to pull the top two per employee to get current and previous?
        • 1. Re: Top 2 records per employee ID
          Frank Kulash
          Hi,

          This question doesn't concern SQL*Plus or iSQL*Plus in any way, does it? It's a SQL question, so, in the future, you might have better luck posting questions like this in the SQL forum:
          SQL and PL/SQL

          The analytic RANK (or, depending on how you want to handle ties, ROW_NUMBER) function is more versatile than aggregate functions for this kind of thing
          Since I don't have a version of your table, I'll use the scott.emp table to illustrate.
          Here's one way to get some information about the two latest rows (in order by hiredate) for each job:
          WITH     got_r_num     AS
          (
               SELECT     job, hiredate, ename, sal
               ,     RANK () OVER ( PARTITION BY  job
                                ORDER BY      hiredate     DESC
                              )     AS r_num
               FROM     scott.emp
          )
          SELECT       *
          FROM       got_r_num
          WHERE       r_num     <= 2
          ORDER BY  job
          ,       hiredate     DESC
          ;
          Output:
          JOB       HIREDATE  ENAME             SAL      R_NUM
          --------- --------- ---------- ---------- ----------
          ANALYST   19-APR-87 SCOTT            3000          1
          ANALYST   03-DEC-81 FORD             3000          2
          
          CLERK     23-MAY-87 ADAMS            1100          1
          CLERK     23-JAN-82 MILLER           1300          2
          
          MANAGER   09-JUN-81 CLARK            2450          1
          MANAGER   01-MAY-81 BLAKE            2850          2
          
          PRESIDENT 17-NOV-81 KING             5000          1
          
          SALESMAN  28-SEP-81 MARTIN           1250          1
          SALESMAN  08-SEP-81 TURNER           1500          2
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          • 2. Re: Top 2 records per employee ID
            654684
            Thank you very much. That was exactly what I needed. Sorry for posting in the wrong forum.