CODEWITHSUNDEEP
sqloracle-databasetop-n
ajf1000
ajf1000

Reputation: 429

How to Get Set of Second Highest Values?

Suppose I have the following table:

    employee_id    salary
    34             100
    22             49
    19             49
    29             30
    17             22

And I want to return the set of employees with the second highest salaries (when there are ties), as follows:

   employee_id    salary
   22             49
   19             49

How would I do that?

Upvotes: 0

Views: 80

Answers (4)

Jayanth
Jayanth

Reputation: 816

I hope this is the easiest of all. Use rownum as it is Oracle.

SELECT t.employee_id, t.salary
FROM
(
    SELECT distinct employee_id, salary, rownum as row from 
    FROM yourTable order by salary desc
) t
WHERE t.row = 2;

Upvotes: 0

eifla001
eifla001

Reputation: 1157

this can be done also in using query below,

scenario 1: Output two records

WITH employee
AS (
SELECT 34 emp_id, 100 rate FROM DUAL
UNION
SELECT 22 emp_id, 49 rate FROM DUAL
UNION
SELECT 19 emp_id, 49 rate FROM DUAL
UNION
SELECT 29 emp_id, 30 rate FROM DUAL
UNION
SELECT 17 emp_id, 22 rate FROM DUAL),
emp_rate_cnt AS 
(SELECT rownum rown, rate, same_rate_count
   FROM (SELECT rate, count(1) same_rate_count
           FROM employee
          GROUP BY rate
          ORDER BY rate DESC))
SELECT *
  FROM employee a
 WHERE exists (SELECT 1
                 FROM emp_rate_cnt b
                WHERE b.rate = a.rate
                  AND b.rown = 2
                  AND b.same_rate_count > 1);

scenario 2: Output no records

WITH employee
AS (
SELECT 34 emp_id, 100 rate FROM DUAL
UNION
SELECT 22 emp_id, 49 rate FROM DUAL
UNION
SELECT 19 emp_id, 50 rate FROM DUAL
UNION
SELECT 29 emp_id, 30 rate FROM DUAL
UNION
SELECT 17 emp_id, 22 rate FROM DUAL),
emp_rate_cnt AS 
(SELECT rownum rown, rate, same_rate_count
   FROM (SELECT rate, count(1) same_rate_count
           FROM employee
          GROUP BY rate
          ORDER BY rate DESC))
SELECT *
  FROM employee a
 WHERE exists (SELECT 1
                 FROM emp_rate_cnt b
                WHERE b.rate = a.rate
                  AND b.rown = 2
                  AND b.same_rate_count > 1);

Upvotes: 0

breakmantis
breakmantis

Reputation: 18

You can use nested query.

Steps taken :

  1. Get all salary values ( sort it and obtain 2nd highest value ) :

SELECT salary FROM employee GROUP BY 1 ORDER BY 1 DESC limit 1 OFFSET 1;

OR can be written as :

SELECT salary FROM employee GROUP BY employee_id ORDER BY employee_id DESC limit 1 OFFSET 1;

Now use the query within employee table

SELECT * FROM employee where salary=(SELECT salary FROM employee GROUP BY 1 ORDER BY 1 DESC limit 1 OFFSET 1);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

Use DENSE_RANK:

SELECT employee_id, salary
FROM
(
    SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) dr
    FROM yourTable
) t
WHERE dr = 2;

Upvotes: 2

Related Questions