7 Replies Latest reply: Dec 3, 2012 2:29 PM by Sven W. RSS

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

    user10941925
      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
          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
            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
              mschnatt
              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
                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
                  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
                    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.
                      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
                      ;