This discussion is archived
7 Replies Latest reply: Dec 3, 2012 12:29 PM by Sven W. RSS

i want to get the second highest salary  of the  person 1

user10941925 Newbie
Currently Being Moderated
Dear All ,


I have record of one person in table PAY_ELEMENT_ENTRY_VALUES_F like , i want to get the second highest salary in of 1 person , can you help me in this regards


SELECT MAX(Effective date) FROM PAY_ELEMENT_ENTRY_VALUES_F


Person_id Person_Name salary Effective date

1 hussain 6000 01-jan-2011

1 hussain 7000 01-mar-2011

1 hussain 8000 01-Oct-2011

1 hussain 9000 01-DEC-2011


Regards
  • 1. Re: i want to get the second highest salary  of the  person 1
    LPS Journeyer
    Currently Being Moderated
    For getting second highest salary and add your flavours in it .
    These are common questions second highest salary of the person 1

    select * from emp a where 2 =( select count(distinct sal) from emp b where b.sal >= a.sal);
  • 2. Re: i want to get the second highest salary  of the  person 1
    BluShadow Guru Moderator
    Currently Being Moderated
    Perhaps like this:
    select person_id, person_name, salary, effective_date
    from (
          select person_id, person_name, salary, effective_date
                ,dense_rank() over (partition by person_id order by salary desc) as r
          from pay_element_entry_values_f
         )
    where r = 2
    (untested)
  • 3. Re: i want to get the second highest salary  of the  person 1
    761512 Newbie
    Currently Being Moderated
    SELECT *
    FROM (SELECT Person_id, Person_name, salary, effective_date, row_number() over (partition by person_id order by salary desc) rn
    FROM pay_element_entry_values_f)
    where person_id = ... and rn = 2;
  • 4. Re: i want to get the second highest salary  of the  person 1
    Ashu_Neo Pro
    Currently Being Moderated
    Blu,
    I guess, PO is asking for one person id = 1.
    Try this
    SELECT * 
    FROM (
    WITH t(p_id, p_name,sal,eff_dt) AS
    (
    SELECT 1 ,'hussain' ,6000, '01-jan-2011' FROM dual UNION ALL
    SELECT 1 ,'hussain' ,7000, '01-mar-2011' FROM dual UNION ALL
    SELECT 1, 'hussain' ,8000 ,'01-Oct-2011' FROM dual UNION ALL
    SELECT 1 ,'hussain' ,9000 ,'01-DEC-2011' FROM dual UNION ALL
    SELECT 2 ,'other1' ,4000 ,'01-DEC-2011' FROM dual  UNION ALL
    SELECT 3 ,'other2' ,5000 ,'01-DEC-2011' FROM dual
    )
    SELECT t.*, DENSE_RANK() OVER(PARTITION BY p_id ORDER BY sal DESC) drn FROM t 
    WHERE p_id = 1  /* For person id 1 only */
    ) WHERE drn = 2
    / 
    Thanks!
  • 5. Re: i want to get the second highest salary  of the  person 1
    BluShadow Guru Moderator
    Currently Being Moderated
    Ashu_Neo wrote:
    Blu,
    I guess, PO is asking for one person id = 1.
    Maybe... maybe not... but that's irrelevant in relation to the question about how to get the 2nd highest salary. It's just a cosmetic addition, if required.
  • 6. Re: i want to get the second highest salary  of the  person 1
    ranit B Expert
    Currently Being Moderated
    WITH t(p_id, p_name,sal,eff_dt) AS
    (
    SELECT 1 ,'hussain' ,6000, '01-jan-2011' FROM dual UNION ALL
    SELECT 1 ,'hussain' ,7000, '01-mar-2011' FROM dual UNION ALL
    SELECT 1, 'hussain' ,8000 ,'01-Oct-2011' FROM dual UNION ALL
    SELECT 1 ,'hussain' ,9000 ,'01-DEC-2011' FROM dual UNION ALL
    SELECT 2 ,'other1' ,4000 ,'01-DEC-2011' FROM dual  UNION ALL
    SELECT 3 ,'other2' ,5000 ,'01-DEC-2011' FROM dual
    )
    SELECT x.* FROM (
        SELECT t.*, ROW_NUMBER () OVER (PARTITION BY p_id ORDER BY sal DESC) rn 
           FROM t WHERE p_id = 1) x
     WHERE x.rn = 2;
    gives
    1     hussain     8000     01-Oct-2011     2
  • 7. Re: i want to get the second highest salary  of the  person 1
    Sven W. Guru
    Currently Being Moderated
    I think in 12c a solution for this statment will look similar to this
    WITH t(p_id, p_name,sal,eff_dt) AS
    (
    SELECT 1 ,'hussain' ,6000, '01-jan-2011' FROM dual UNION ALL
    SELECT 1 ,'hussain' ,7000, '01-mar-2011' FROM dual UNION ALL
    SELECT 1, 'hussain' ,8000 ,'01-Oct-2011' FROM dual UNION ALL
    SELECT 1 ,'hussain' ,9000 ,'01-DEC-2011' FROM dual UNION ALL
    SELECT 2 ,'other1' ,4000 ,'01-DEC-2011' FROM dual  UNION ALL
    SELECT 3 ,'other2' ,5000 ,'01-DEC-2011' FROM dual
    )
    select * from t
    where p_id = 1
    order by sal desc
    OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY
    ;

Legend

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