0

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
ajf1000
  • 409
  • 5
  • 13

4 Answers4

2

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Didn't realize I could use DENSE_RANK() OVER without partition. Thanks! – ajf1000 Feb 11 '18 at 02:14
  • @ajf1000 - This is true for most analytic functions; "without partition" means all the rows are part of a single partition. (Equivalently, if you want to include `partition by` explicitly, to show to the next developer reading the code in the future that you didn't forget it, you could `partition by null` - with the same effect, of all the rows being a single partition.) –  Feb 11 '18 at 19:49
0

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);

breakmantis
  • 18
  • 2
  • Oracle doesn't support `LIMIT`; that is MySQL syntax. – Tim Biegeleisen Feb 11 '18 at 02:26
  • @TimBiegeleisen - Oracle 12.1 introduced a row limiting clause (with a different syntax); the bigger issue with this answer is the handling of ties, which is mentioned explicitly in the OP's question. –  Feb 11 '18 at 19:47
  • @mathguy Yes, ties can be an issue, buf I'd rather bs using an analytic function in that case. – Tim Biegeleisen Feb 11 '18 at 23:09
  • @TimBiegeleisen - That was my point too: even if a "limit 1" kind of syntax was correct (it is, in 12.1 and higher - it is just different), it would still not solve the OP's problem. Your answer with `dense_rank()` is the correct one. –  Feb 11 '18 at 23:13
  • @mathguy What is the issue with ties? What its missing? – breakmantis Feb 12 '18 at 09:09
  • @breakmantis - The OP mentioned specifically the possibility of ties. If the highest salary is, say, 120, and there are THREE employees with this salary, then your first query will produce the value 120. This is because OFFSET and LIMIT, or the ORACLE equivalent, doesn't handle ties. Just try it: add three more rows to the OP's test data, all with a salary of 120, and see what happens (in whatever database you have, with the LIMIT operator - that wouldn't be Oracle). –  Feb 12 '18 at 16:26
  • @mathguy, I have used GROUP BY query, before the OFFSET and LIMIT. I'm using mysql and I agree this is not acceptable answer for Oracle. However, I dont see any issues in handling ties. My first query will produce the 2nd highest values. Here is test data and result : http://rextester.com/CEYVK35082 – breakmantis Feb 13 '18 at 03:53
0

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);
eifla001
  • 1,137
  • 8
  • 8
0

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;
Jayanth
  • 746
  • 6
  • 17