CODEWITHSUNDEEP
sql
user18714945
user18714945

Reputation:

Second largest salary in SQL from a joined table

How to find the second largest value from two tables combined? I have written the following code but i would like something smaller and more efficient.

SELECT MAX(salary) 
FROM (
    SELECT salary FROM employees
    UNION
    SELECT salary FROM professors
) X
WHERE salary < (
            SELECT MAX(salary)
            FROM (
                SELECT salary FROM employees
                UNION
                SELECT salary FROM professors
                ) Y
            );

Upvotes: 0

Views: 159

Answers (1)

Stephan
Stephan

Reputation: 6018

Depending on your DBMS, the syntax may vary slightly, but need to grab 1 row offset by 1 row.

This is probably fastest way to do it. Could also use WHERE ROW_NUMBER() = 2, but that will require scanning all your data.

2nd Highest Value from 1 Table

SELECT *
FROM hil.employees
ORDER BY Salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY

Not sure goal of your query, but appears you might be wanting to grab the 2nd highest between the two tables. Keep in mind, your UNION removes any duplicate values, which may or may not be desirable

2nd Highest of Distinct Values of Both Tables

SELECT Salary
FROM hil.employees
UNION 
SELECT Salary
FROM hil.professors
ORDER BY Salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY

Upvotes: 2

Related Questions