This discussion is archived
2 Replies Latest reply: Sep 2, 2011 11:50 AM by 654684 RSS

Top 2 records per employee ID

654684 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you very much. That was exactly what I needed. Sorry for posting in the wrong forum.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points