CODEWITHSUNDEEP
sqlpostgresql
user8023293
user8023293

Reputation: 67

What is a simple SQL query to select the ID of a person with the SECOND highest salary

If I had a relation: employee(id, salary). What would be the simplest SQL query to select the ID of the person with the second highest salary.

I know how to select the highest salary using:

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary)
FROM employee);

but how would you select the ID of that person, and display only the ID of the person with the second highest salary (without using LIMIT).

Upvotes: 0

Views: 198

Answers (4)

bobflux
bobflux

Reputation: 11581

The optimal answer depends on the existence of an index on salary. I have setup a sqlfiddle which loads 100k rows in a table.

With an index, my answer would be:

SELECT * FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;

This is the simplest, shortest and cleanest one. It is also the fastest, at 40µs, as it uses an Index Scan Backwards. However, if there is a tie-in for second position, it will only return one row.

Still with an index, the three above queries with max() in subqueries are a close second. Bob Jarvis' scores 100µs.

Unfortunately, Gordon's window function hits a snag as postgres scans the entire index (well, at least it does not do a sort):

Subquery Scan on e (cost=0.29..7518.27 rows=500 width=20) (actual time=0.042..137.810 rows=1 loops=1)
Filter: (e.seqnum = 2)
Rows Removed by Filter: 99999
-> WindowAgg (cost=0.29..6268.27 rows=100000 width=20) (actual time=0.029..121.445 rows=100000 loops=1)
-> Index Scan Backward using emps_sal on employee e_1 (cost=0.29..4768.27 rows=100000 width=12)
 (actual time=0.022..54.861 rows=100000 loops=1)

This takes 137ms which is quite long. I believe the root cause to be that postgres treats "seqnum=2" as any other WHERE expression and thus does not realize it could stop after the second row. We can nudge it by adding a "LIMIT 1" in which case it does stop after the second row, and takes only 70µs, so it is very fast.

Now, without an index...

My query with the ORDER BY does a sort, however it is postgres' glorious top-n heapsort which is fast: 34ms.

Gordon't window function does a full sort, which takes 108ms.

Bob's query does not sort, but it scans the table 3 times: 84 ms.

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

SELECT ID
  FROM EMPLOYEE
  WHERE SALARY = (SELECT MAX(salary) AS SECOND_HIGHEST_SALARY
                    FROM employee
                    WHERE salary < (SELECT MAX(salary) AS HIGHEST_SALARY
                                      FROM employee)

This will give you the IDs of all employees who have the second-highest salary. If you only want one employee use SELECT MIN(ID) on the outer query.

Upvotes: 0

user8280843
user8280843

Reputation: 

SELECT *
FROM employee
WHERE salary = (
  SELECT max(salary)
  FROM employee
  WHERE salary != (
    SELECT max(salary)
    FROM employee
  )
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I would use row_number() or rank(), depending on what you mean by second highest salary:

SELECT e.*
FROM (SELECT e.*, RANK() OVER (ORDER BY salary DESC) as seqnum
      FROM employee e
     ) e
WHERE seqnum = 2;

If you want to use your query -- and this seems way more complicated -- you can use subqueries:

SELECT e.*
FROM employee e
WHERE e.salary = (SELECT MAX(e2.salary)
                  FROM employee e2
                  WHERE e2.salary < (SELECT MAX(e3.salary)
                                     FROM employee e3
                                    )
                 );

It is nice to see that SQL has made some progress in the last 30 years.

Upvotes: 0

Related Questions