1

I already get it by doing this query:

   SELECT * 
    FROM
      (
      SELECT emp_id,salary,row_number() over(order by salary  desc) AS rk 
      FROM test_qaium
      ) 
    where rk=2;

But one of my friend ask me to find second MAX salary from employees table must using "over(partition by )" in oracle sql. Anybody please help me. And clear me the concept of "Partition by" in oracle sql.

Muhammad Abdul Qaium
  • 121
  • 1
  • 2
  • 10
  • will you please provide me a query ? – Muhammad Abdul Qaium Oct 23 '17 at 09:00
  • Your query seems to do what you are describing. What exactly is your problem? –  Oct 23 '17 at 09:08
  • Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. –  Oct 23 '17 at 09:08

2 Answers2

3

Oracle Setup:

CREATE TABLE test_qaium ( emp_id, salary, department_id ) AS
  SELECT  1, 10000, 1 FROM DUAL UNION ALL
  SELECT  2, 20000, 1 FROM DUAL UNION ALL
  SELECT  3, 30000, 1 FROM DUAL UNION ALL
  SELECT  4, 40000, 1 FROM DUAL UNION ALL -- One highest, one 2nd highest
  SELECT  5, 10000, 2 FROM DUAL UNION ALL
  SELECT  6, 20000, 2 FROM DUAL UNION ALL
  SELECT  7, 30000, 2 FROM DUAL UNION ALL
  SELECT  8, 30000, 2 FROM DUAL UNION ALL -- Two highest, one 2nd highest
  SELECT  9, 10000, 3 FROM DUAL UNION ALL
  SELECT 10, 10000, 3 FROM DUAL UNION ALL -- Two highest, no 2nd highest
  SELECT 11, 10000, 4 FROM DUAL UNION ALL -- One highest, no 2nd highest
  SELECT 12, 20000, 5 FROM DUAL UNION ALL
  SELECT 13, 20000, 5 FROM DUAL UNION ALL
  SELECT 14, 30000, 5 FROM DUAL;          -- One highest, Two 2nd highest

Query:

This will get all the rows with the 2nd highest salary for each department:

SELECT * 
FROM   (
  SELECT t.*,
         DENSE_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS rnk 
  FROM   test_qaium t
) 
WHERE  rnk=2;

Output:

EMP_ID SALARY DEPARTMENT_ID RNK
------ ------ ------------- ---
     3  30000             1   2
     6  20000             2   2
    12  20000             5   2
    13  20000             5   2
MT0
  • 143,790
  • 11
  • 59
  • 117
2

If you have multiple employees on the same salary, your query will not work...

Try:

SELECT emp_id,
       salary,
       dense_rank() over(order by salary desc) AS rk 
FROM test_qaium

The partition by clause works like a group by, and there's nothing here that needs grouping.

JohnHC
  • 10,935
  • 1
  • 24
  • 40