LeetCode 176 Second Highest Salary defines a single table Employee with an id column and a salary column and asks for a query that returns the second highest distinct salary value. When the table holds only one salary level, or is empty, the result is NULL. Multiple employees can share the same salary, so the query cannot rely on row counts and must treat salaries as levels, not as separate values per employee.
For an Oracle SQL solution, the problem reduces to ranking or filtering salary levels so that the value directly below the maximum salary appears in a single scalar query. Two common query forms are aggregate based logic that locates the maximum salary and then the maximum value below it, and a sorted list of distinct salaries combined with ROWNUM filtering that assigns row numbers in descending order and then selects the row labeled as the second level. Indexes on salary work with both forms, because they allow Oracle to read high salary values from the top of a B tree without scanning the entire table.
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.
Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).
A pair of aggregates can answer the question without any explicit sort. One pass finds the highest salary, and another finds the highest salary that is still below that value.
SELECT
(
SELECT MAX(salary)
FROM Employee
WHERE salary < (
SELECT MAX(salary)
FROM Employee
)
) AS SecondHighestSalary
FROM dual;
Let’s walk through what each part does step by step:
SELECT MAX(salary)
FROM Employee
This inner subquery finds the largest salary in the whole table. Oracle scans the Employee rows or walks a salary index to pick out that single top value.
WHERE salary < (
SELECT MAX(salary)
FROM Employee
)
Back in the middle layer, this filter keeps only rows whose salary is strictly less than that top value. Any row that carries the highest salary level is removed. If all rows share the same salary, nothing survives this filter.
SELECT MAX(salary)
This aggregate now runs over the filtered rows. It asks for the largest salary among the rows that remain, which lines up with the second highest salary as long as there is at least one lower level.
AS SecondHighestSalary
The result of that aggregate is given the name SecondHighestSalary, which becomes the column label in the final output.
FROM dual
Oracle needs a row source for the outer query. The dual table supplies a single row so the scalar subquery can be evaluated once, returning either a number or NULL when no lower salary exists.
Aggregation here keeps the work focused on streaming passes through the data. Without an index on salary, Oracle evaluates the inner MAX in roughly O(N) time and the outer MAX in another O(N) pass, with O(1) extra memory because only a few aggregate variables are tracked while rows flow through the engine. With a B tree index on salary, the inner MAX can be satisfied by a quick jump to the last leaf block in the index, and the outer MAX can scan only the part of the index that holds values below that top salary, which shortens the path in common cases while keeping the worst case near linear time. Many interviewers like this solution because the SQL text stays short, the control flow is easy to describe in words, and it gives a natural opening to talk about how scalar subqueries, filters, and index order interact in Oracle.
Solution 2: ORDER BY with ROWNUM Tagging
This version ranks distinct salaries. It sorts the unique salary levels from highest to lowest, tags them with ROWNUM, then picks the one tagged as number two.
SELECT
(
SELECT salary
FROM (
SELECT salary, ROWNUM AS rn
FROM (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
)
WHERE ROWNUM <= 2
)
WHERE rn = 2
) AS SecondHighestSalary
FROM dual;
Now break this query down:
SELECT DISTINCT salary
FROM Employee
This inner query reads every row from Employee and builds a list of unique salary values. When several workers share the same salary, that amount appears once in this list, so the logic focuses on salary levels rather than headcount.
ORDER BY salary DESC
The distinct salaries are sorted from highest to lowest. The largest salary becomes the first row in this ordered list, the next largest becomes the second row, and so on down the column.
SELECT salary, ROWNUM AS rn
FROM (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
)
The next layer reads from that ordered list and assigns ROWNUM as rows come out. The first salary in descending order gets rn = 1, the second gets rn = 2, and the rest follow that sequence.
WHERE ROWNUM <= 2
This filter keeps only the first two rows from that stream. At this point there are at most two records: one holding the highest salary with rn = 1 and one holding the second highest salary with rn = 2. If only one salary level exists, there is only the rn = 1 row.
SELECT salary
FROM (
SELECT salary, ROWNUM AS rn
FROM (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
)
WHERE ROWNUM <= 2
)
WHERE rn = 2
The outer part of the scalar subquery reads from that small two-row view and filters on rn = 2. That picks out the second highest salary when it exists. If the filtered view never had a row with rn = 2, this select returns no row and the scalar expression evaluates to NULL.
AS SecondHighestSalary
The selected salary is named SecondHighestSalary, matching the required output column label.
FROM dual
As before, dual provides the single row that allows the scalar subquery to be evaluated once.
For this solution, the sort of distinct salaries is the main cost. With D distinct salary values, Oracle spends about O(D log D) time and O(D) memory to build and sort the list in a temporary segment, while the ROWNUM tagging and outer filters add only a small constant amount of extra work and memory after the sorted list has been produced. When a B tree index exists on salary, the engine can stream distinct values in descending order straight from the index and stop after it has seen the second distinct value, which cuts the practical cost down toward O(log N) time and O(1) memory for many data sets. Compared with the aggregate solution, this one trades extra sorting overhead for a very explicit ranking model that shows how Oracle assigns ROWNUM after the ordered inline view, why the ROWNUM <= 2 filter must live in an inner query, and how an index on salary feeds that ranking, so it can fit well in interviews that focus on Oracle execution details and time and space tradeoffs between alternative queries.
