Find Second Highest Salary - Five Different Approaches

🎯

Find Second Highest Salary - Five Different Approaches

mediumDBMSAmazonGoogleMicrosoft

Imagine you are managing employee payroll and need to find the second highest salary to determine bonus eligibility or tax brackets.

💡 This problem is a classic SQL query challenge that tests your understanding of ranking, subqueries, and window functions. Beginners often struggle because there are multiple ways to solve it, each with different performance and readability tradeoffs.

📋

Problem Statement

Given a table Employee with columns Id and Salary, write a SQL query to find the second highest salary from the Employee table. If there is no second highest salary, return NULL.

1 ≤ number of employees ≤ 10^5Salary values are positive integersThere may be duplicate salaries

💡

Example

Input"Employee table:\nId | Salary\n1 | 100\n2 | 200\n3 | 300"

Output200

The highest salary is 300, the second highest is 200.

Input"Employee table:\nId | Salary\n1 | 100"

OutputNULL

Only one salary exists, so second highest does not exist.

• All employees have the same salary → output NULL
• Only one employee in the table → output NULL
• Multiple employees share the highest salary → second highest is next distinct salary
• Salaries are not consecutive numbers → still find second distinct highest

⚠️

Common Mistakes

Using MAX(Salary) twice without excluding highest salary

Returns highest salary again instead of second highest

✅ Exclude highest salary using NOT IN or subquery

Using LIMIT 1 OFFSET 1 without DISTINCT

Returns duplicate highest salary as second highest

✅ Use DISTINCT to remove duplicates before ordering

Using ROW_NUMBER when duplicates exist expecting ties

Returns a salary that is not the second distinct highest

✅ Use DENSE_RANK to handle ties correctly

Not handling NULL when no second highest salary exists

Query returns no rows or errors

✅ Return NULL explicitly or handle empty result sets

Assuming LIMIT OFFSET syntax works in all SQL dialects

Query fails or behaves unexpectedly in some databases

✅ Check SQL dialect support or use window functions

🧠

Brute Force Using Subquery with MAX and NOT IN

💡 This approach uses basic SQL constructs and is easy to understand but inefficient for large datasets. It helps beginners grasp the problem by explicitly excluding the highest salary.

Intuition

Find the maximum salary first, then find the maximum salary that is less than the highest salary by excluding it.

Algorithm

1. Find the maximum salary from the Employee table.
2. Exclude this maximum salary from the set of salaries.
3. Find the maximum salary from the remaining salaries.
4. If no such salary exists, return NULL.

💡 This approach is straightforward but requires two passes over the data and careful handling of NULL when no second highest exists.

</>

Code

import sqlite3

# Setup in-memory database and Employee table
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')
cursor.executemany('INSERT INTO Employee VALUES (?, ?)', [(1, 100), (2, 200), (3, 300)])

# Query to find second highest salary
query = '''
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)
'''
cursor.execute(query)
result = cursor.fetchone()[0]
print(result if result is not None else 'NULL')

# Close connection
conn.close()

Line Notes

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')Create Employee table schema for testing to hold employee data.

cursor.executemany('INSERT INTO Employee VALUES (?, ?)'Insert sample employee salary data to demonstrate the query.

SELECT MAX(Salary) AS SecondHighestSalarySelect the maximum salary excluding the highest salary to find the second highest.

WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)Exclude the highest salary from the selection to avoid returning it again.

import java.sql.*;

public class SecondHighestSalary {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE Employee(Id INTEGER, Salary INTEGER)");
        stmt.execute("INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300)");

        String query = "SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)";
        ResultSet rs = stmt.executeQuery(query);
        if (rs.next()) {
            int res = rs.getInt("SecondHighestSalary");
            System.out.println(rs.wasNull() ? "NULL" : res);
        }
        rs.close();
        stmt.close();
        conn.close();
    }
}

Line Notes

Connection conn = DriverManager.getConnectionCreate in-memory SQLite connection for testing the query.

stmt.execute("CREATE TABLE EmployeeCreate Employee table schema to store employee salaries.

stmt.execute("INSERT INTO Employee VALUESInsert sample employee salary data for demonstration.

SELECT MAX(Salary) AS SecondHighestSalaryQuery to find the second highest salary by excluding the highest.

#include <iostream>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    char *errMsg = nullptr;
    sqlite3_exec(db, "CREATE TABLE Employee(Id INTEGER, Salary INTEGER);", nullptr, nullptr, &errMsg);
    sqlite3_exec(db, "INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300);", nullptr, nullptr, &errMsg);

    const char *sql = "SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);";
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        if (sqlite3_column_type(stmt, 0) == SQLITE_NULL) std::cout << "NULL" << std::endl;
        else std::cout << sqlite3_column_int(stmt, 0) << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

Line Notes

sqlite3_open(":memory:", &db);Open an in-memory SQLite database for testing purposes.

sqlite3_exec(db, "CREATE TABLE EmployeeCreate Employee table schema to hold employee salary data.

sqlite3_exec(db, "INSERT INTO Employee VALUESInsert sample employee salary data for the query.

SELECT MAX(Salary) AS SecondHighestSalaryQuery to find the second highest salary excluding the maximum.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)');
  db.run('INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300)');

  const query = `
    SELECT MAX(Salary) AS SecondHighestSalary
    FROM Employee
    WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)
  `;

  db.get(query, (err, row) => {
    if (err) throw err;
    console.log(row.SecondHighestSalary === null ? 'NULL' : row.SecondHighestSalary);
  });
});

db.close();

Line Notes

db.run('CREATE TABLE EmployeeCreate Employee table schema in the in-memory database.

db.run('INSERT INTO Employee VALUESInsert sample employee salary data for testing.

SELECT MAX(Salary) AS SecondHighestSalaryQuery to find the second highest salary by excluding the highest.

console.log(row.SecondHighestSalary === null ? 'NULL'Print NULL if no second highest salary exists, else print the salary.

⏱

Complexity

TimeO(n) for scanning salaries twice

SpaceO(1) additional space

The query scans the Employee table twice: once to find the max salary, once to find the max excluding that salary.

💡 For 100,000 employees, this means roughly 200,000 salary reads, which is acceptable but not optimal.

Interview Verdict: Accepted

This approach works but is not efficient for very large datasets or complex queries.

🧠

Using LIMIT and OFFSET (MySQL Specific)

💡 This approach leverages MySQL's LIMIT and OFFSET clauses to directly skip the highest salary and fetch the next one. It is concise but not portable across all SQL dialects.

Intuition

Order salaries descending, skip the highest salary by offsetting one row, and fetch the next row which is the second highest.

Algorithm

1. Order the Employee table by Salary in descending order.
2. Skip the first row (highest salary) using OFFSET 1.
3. Fetch the next row using LIMIT 1.
4. Return the salary or NULL if no such row exists.

💡 This approach is simple but depends on SQL dialect support and does not handle duplicates automatically.

</>

Code

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')
cursor.executemany('INSERT INTO Employee VALUES (?, ?)', [(1, 100), (2, 200), (3, 300)])

# SQLite does not support OFFSET with LIMIT in subqueries easily, so simulate with ROW_NUMBER
query = '''
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1
'''
cursor.execute(query)
result = cursor.fetchone()
print(result[0] if result else 'NULL')
conn.close()

Line Notes

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')Create Employee table for testing to hold employee data.

cursor.executemany('INSERT INTO Employee VALUES (?, ?)')Insert sample employee salary data for demonstration.

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1Fetch the second highest salary by ordering descending and skipping the first.

print(result[0] if result else 'NULL')Print the salary if found; otherwise print NULL.

import java.sql.*;

public class SecondHighestSalaryLimitOffset {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE Employee(Id INTEGER, Salary INTEGER)");
        stmt.execute("INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300)");

        String query = "SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1";
        ResultSet rs = stmt.executeQuery(query);
        if (rs.next()) {
            System.out.println(rs.getInt("Salary"));
        } else {
            System.out.println("NULL");
        }
        rs.close();
        stmt.close();
        conn.close();
    }
}

Line Notes

Connection conn = DriverManager.getConnectionCreate in-memory SQLite connection for testing.

stmt.execute("CREATE TABLE EmployeeCreate Employee table schema to hold salaries.

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1Query to get the second highest salary by offsetting the first.

System.out.println(rs.getInt("Salary"))Print the second highest salary if it exists.

#include <iostream>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    char *errMsg = nullptr;
    sqlite3_exec(db, "CREATE TABLE Employee(Id INTEGER, Salary INTEGER);", nullptr, nullptr, &errMsg);
    sqlite3_exec(db, "INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300);", nullptr, nullptr, &errMsg);

    const char *sql = "SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1;";
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        std::cout << sqlite3_column_int(stmt, 0) << std::endl;
    } else {
        std::cout << "NULL" << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

Line Notes

sqlite3_open(":memory:", &db);Open in-memory SQLite database for testing.

sqlite3_exec(db, "CREATE TABLE EmployeeCreate Employee table schema.

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1;Query to get second highest salary by offset.

std::cout << sqlite3_column_int(stmt, 0)Print the second highest salary if found.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)');
  db.run('INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300)');

  const query = `SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1`;

  db.get(query, (err, row) => {
    if (err) throw err;
    console.log(row ? row.Salary : 'NULL');
  });
});

db.close();

Line Notes

db.run('CREATE TABLE EmployeeCreate Employee table schema in memory.

db.run('INSERT INTO Employee VALUESInsert sample employee salary data.

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1Query to get second highest salary by offset.

console.log(row ? row.Salary : 'NULL')Print the salary if found; otherwise print NULL.

⏱

Complexity

TimeO(n log n) due to sorting

SpaceO(1) additional space

Ordering the salaries requires sorting which is O(n log n), then fetching one row is O(1).

💡 For 100,000 employees, sorting is more expensive than scanning twice but still feasible.

Interview Verdict: Accepted (MySQL specific)

This approach is concise but not portable to all SQL dialects and does not handle duplicates automatically.

🧠

Using DENSE_RANK() Window Function

💡 Window functions like DENSE_RANK provide a powerful and elegant way to rank rows and handle duplicates correctly. This approach is optimal and widely supported in modern SQL.

Intuition

Assign ranks to salaries in descending order, with ties getting the same rank. Then select the salary with rank 2.

Algorithm

1. Use DENSE_RANK() over salaries ordered descending to assign ranks.
2. Filter rows where rank equals 2.
3. Select the salary from these rows.
4. Return NULL if no such row exists.

💡 This approach handles duplicates naturally and is efficient with indexing.

</>

Code

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')
cursor.executemany('INSERT INTO Employee VALUES (?, ?)', [(1, 100), (2, 200), (3, 300), (4, 300)])

query = '''
SELECT Salary FROM (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
  FROM Employee
) WHERE rnk = 2
'''
cursor.execute(query)
result = cursor.fetchone()
print(result[0] if result else 'NULL')
conn.close()

Line Notes

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')Create Employee table to hold employee salary data.

cursor.executemany('INSERT INTO Employee VALUES (?, ?)')Insert sample data including duplicate salaries to test ranking.

DENSE_RANK() OVER (ORDER BY Salary DESC)Assign ranks to salaries in descending order, with ties sharing the same rank.

WHERE rnk = 2Filter to select only the second highest salary rank.

import java.sql.*;

public class SecondHighestSalaryDenseRank {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE Employee(Id INTEGER, Salary INTEGER)");
        stmt.execute("INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300)");

        String query = "SELECT Salary FROM (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk FROM Employee) WHERE rnk = 2";
        ResultSet rs = stmt.executeQuery(query);
        if (rs.next()) {
            System.out.println(rs.getInt("Salary"));
        } else {
            System.out.println("NULL");
        }
        rs.close();
        stmt.close();
        conn.close();
    }
}

Line Notes

Connection conn = DriverManager.getConnectionCreate in-memory SQLite connection for testing.

stmt.execute("CREATE TABLE EmployeeCreate Employee table schema.

DENSE_RANK() OVER (ORDER BY Salary DESC)Assign ranks to salaries with ties sharing the same rank.

WHERE rnk = 2Filter to get the second highest salary rank.

#include <iostream>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    char *errMsg = nullptr;
    sqlite3_exec(db, "CREATE TABLE Employee(Id INTEGER, Salary INTEGER);", nullptr, nullptr, &errMsg);
    sqlite3_exec(db, "INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300);", nullptr, nullptr, &errMsg);

    const char *sql = "SELECT Salary FROM (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk FROM Employee) WHERE rnk = 2;";
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        std::cout << sqlite3_column_int(stmt, 0) << std::endl;
    } else {
        std::cout << "NULL" << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

Line Notes

sqlite3_open(":memory:", &db);Open in-memory SQLite database for testing.

sqlite3_exec(db, "CREATE TABLE EmployeeCreate Employee table schema.

DENSE_RANK() OVER (ORDER BY Salary DESC)Assign ranks to salaries with ties sharing the same rank.

WHERE rnk = 2Filter to select the second highest salary rank.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)');
  db.run('INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300)');

  const query = `
    SELECT Salary FROM (
      SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
      FROM Employee
    ) WHERE rnk = 2
  `;

  db.get(query, (err, row) => {
    if (err) throw err;
    console.log(row ? row.Salary : 'NULL');
  });
});

db.close();

Line Notes

db.run('CREATE TABLE EmployeeCreate Employee table schema.

db.run('INSERT INTO Employee VALUESInsert sample data including duplicates to test ranking.

DENSE_RANK() OVER (ORDER BY Salary DESC)Assign ranks to salaries with ties sharing the same rank.

WHERE rnk = 2Filter to select the second highest salary rank.

⏱

Complexity

TimeO(n log n) due to sorting in window function

SpaceO(n) for window function state

Window functions require sorting and maintaining rank state, but handle duplicates elegantly.

💡 For 100,000 employees, this is efficient and scalable with proper indexing.

Interview Verdict: Accepted

This is the preferred approach in modern SQL for clarity and correctness.

🧠

Using ROW_NUMBER() Window Function

💡 ROW_NUMBER assigns unique ranks even for duplicates, so this approach finds the second highest distinct salary by ordering and filtering. It is useful when you want strict ordering without ties.

Intuition

Assign a unique row number to each salary ordered descending, then select the salary with row number 2.

Algorithm

1. Use ROW_NUMBER() over salaries ordered descending to assign unique ranks.
2. Filter rows where row number equals 2.
3. Select the salary from these rows.
4. Return NULL if no such row exists.

💡 This approach does not handle duplicates as ties but is useful for strict ordering.

</>

Code

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')
cursor.executemany('INSERT INTO Employee VALUES (?, ?)', [(1, 100), (2, 200), (3, 300), (4, 300)])

query = '''
SELECT Salary FROM (
  SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
  FROM Employee
) WHERE rn = 2
'''
cursor.execute(query)
result = cursor.fetchone()
print(result[0] if result else 'NULL')
conn.close()

Line Notes

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')Create Employee table to hold employee salary data.

cursor.executemany('INSERT INTO Employee VALUES (?, ?)')Insert sample data including duplicates to test unique ranking.

ROW_NUMBER() OVER (ORDER BY Salary DESC)Assign unique row numbers ordered by salary descending, ignoring ties.

WHERE rn = 2Filter to select the row with unique rank 2.

import java.sql.*;

public class SecondHighestSalaryRowNumber {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE Employee(Id INTEGER, Salary INTEGER)");
        stmt.execute("INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300)");

        String query = "SELECT Salary FROM (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn FROM Employee) WHERE rn = 2";
        ResultSet rs = stmt.executeQuery(query);
        if (rs.next()) {
            System.out.println(rs.getInt("Salary"));
        } else {
            System.out.println("NULL");
        }
        rs.close();
        stmt.close();
        conn.close();
    }
}

Line Notes

Connection conn = DriverManager.getConnectionCreate in-memory SQLite connection for testing.

stmt.execute("CREATE TABLE EmployeeCreate Employee table schema.

ROW_NUMBER() OVER (ORDER BY Salary DESC)Assign unique row numbers ordered descending, ignoring ties.

WHERE rn = 2Filter to select the second unique rank.

#include <iostream>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    char *errMsg = nullptr;
    sqlite3_exec(db, "CREATE TABLE Employee(Id INTEGER, Salary INTEGER);", nullptr, nullptr, &errMsg);
    sqlite3_exec(db, "INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300);", nullptr, nullptr, &errMsg);

    const char *sql = "SELECT Salary FROM (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn FROM Employee) WHERE rn = 2;";
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        std::cout << sqlite3_column_int(stmt, 0) << std::endl;
    } else {
        std::cout << "NULL" << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

Line Notes

sqlite3_open(":memory:", &db);Open in-memory SQLite database for testing.

sqlite3_exec(db, "CREATE TABLE EmployeeCreate Employee table schema.

ROW_NUMBER() OVER (ORDER BY Salary DESC)Assign unique row numbers ordered descending, ignoring ties.

WHERE rn = 2Filter to select the second unique rank.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)');
  db.run('INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300)');

  const query = `
    SELECT Salary FROM (
      SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
      FROM Employee
    ) WHERE rn = 2
  `;

  db.get(query, (err, row) => {
    if (err) throw err;
    console.log(row ? row.Salary : 'NULL');
  });
});

db.close();

Line Notes

db.run('CREATE TABLE EmployeeCreate Employee table schema.

db.run('INSERT INTO Employee VALUESInsert sample data including duplicates to test unique ranking.

ROW_NUMBER() OVER (ORDER BY Salary DESC)Assign unique row numbers ordered descending, ignoring ties.

WHERE rn = 2Filter to select the second unique rank.

⏱

Complexity

TimeO(n log n) due to sorting in window function

SpaceO(n) for window function state

ROW_NUMBER requires sorting and unique numbering, which is efficient but differs from DENSE_RANK in handling duplicates.

💡 For 100,000 employees, this approach is scalable but may not match the problem's requirement if duplicates matter.

Interview Verdict: Accepted

Use this approach if unique ranking is desired; otherwise, DENSE_RANK is preferred.

🧠

Using DISTINCT and ORDER BY with LIMIT and OFFSET

💡 This approach first selects distinct salaries, orders them descending, then uses LIMIT and OFFSET to get the second highest. It is portable and handles duplicates by removing them first.

Intuition

Remove duplicates by selecting distinct salaries, then pick the second highest by ordering and offsetting.

Algorithm

1. Select distinct salaries from Employee.
2. Order these salaries in descending order.
3. Skip the first salary using OFFSET 1.
4. Fetch the next salary using LIMIT 1.
5. Return NULL if no such salary exists.

💡 This approach explicitly removes duplicates before ordering, ensuring correct second highest salary.

</>

Code

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')
cursor.executemany('INSERT INTO Employee VALUES (?, ?)', [(1, 100), (2, 200), (3, 300), (4, 300)])

query = '''
SELECT Salary FROM (
  SELECT DISTINCT Salary FROM Employee
  ORDER BY Salary DESC
  LIMIT 1 OFFSET 1
)
'''
cursor.execute(query)
result = cursor.fetchone()
print(result[0] if result else 'NULL')
conn.close()

Line Notes

cursor.execute('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)')Create Employee table to hold employee salary data.

cursor.executemany('INSERT INTO Employee VALUES (?, ?)')Insert sample data including duplicates to test distinct selection.

SELECT DISTINCT Salary FROM EmployeeRemove duplicate salaries to get unique salary values.

ORDER BY Salary DESC LIMIT 1 OFFSET 1Order salaries descending and pick the second highest distinct salary.

import java.sql.*;

public class SecondHighestSalaryDistinct {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE Employee(Id INTEGER, Salary INTEGER)");
        stmt.execute("INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300)");

        String query = "SELECT Salary FROM (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1)";
        ResultSet rs = stmt.executeQuery(query);
        if (rs.next()) {
            System.out.println(rs.getInt("Salary"));
        } else {
            System.out.println("NULL");
        }
        rs.close();
        stmt.close();
        conn.close();
    }
}

Line Notes

Connection conn = DriverManager.getConnectionCreate in-memory SQLite connection for testing.

stmt.execute("CREATE TABLE EmployeeCreate Employee table schema.

SELECT DISTINCT Salary FROM EmployeeRemove duplicate salaries to get unique salary values.

ORDER BY Salary DESC LIMIT 1 OFFSET 1Order salaries descending and pick the second highest distinct salary.

#include <iostream>
#include <sqlite3.h>

int main() {
    sqlite3 *db;
    sqlite3_open(":memory:", &db);
    char *errMsg = nullptr;
    sqlite3_exec(db, "CREATE TABLE Employee(Id INTEGER, Salary INTEGER);", nullptr, nullptr, &errMsg);
    sqlite3_exec(db, "INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300);", nullptr, nullptr, &errMsg);

    const char *sql = "SELECT Salary FROM (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1);";
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        std::cout << sqlite3_column_int(stmt, 0) << std::endl;
    } else {
        std::cout << "NULL" << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

Line Notes

sqlite3_open(":memory:", &db);Open in-memory SQLite database for testing.

sqlite3_exec(db, "CREATE TABLE EmployeeCreate Employee table schema.

SELECT DISTINCT Salary FROM EmployeeRemove duplicate salaries to get unique salary values.

ORDER BY Salary DESC LIMIT 1 OFFSET 1Order salaries descending and pick the second highest distinct salary.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run('CREATE TABLE Employee(Id INTEGER, Salary INTEGER)');
  db.run('INSERT INTO Employee VALUES (1, 100), (2, 200), (3, 300), (4, 300)');

  const query = `
    SELECT Salary FROM (
      SELECT DISTINCT Salary FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET 1
    )
  `;

  db.get(query, (err, row) => {
    if (err) throw err;
    console.log(row ? row.Salary : 'NULL');
  });
});

db.close();

Line Notes

db.run('CREATE TABLE EmployeeCreate Employee table schema.

db.run('INSERT INTO Employee VALUESInsert sample data including duplicates to test distinct selection.

SELECT DISTINCT Salary FROM EmployeeRemove duplicate salaries to get unique salary values.

ORDER BY Salary DESC LIMIT 1 OFFSET 1Order salaries descending and pick the second highest distinct salary.

⏱

Complexity

TimeO(n log n) due to sorting distinct salaries

SpaceO(n) for distinct salaries

Removing duplicates and sorting distinct salaries requires sorting, but reduces data size if many duplicates exist.

💡 For large datasets with many duplicates, this approach can be more efficient than sorting all rows.

Interview Verdict: Accepted

This approach is portable and handles duplicates correctly without window functions.

📊

All Approaches - One-Glance Tradeoffs

💡 For most interviews, approach 3 using DENSE_RANK is recommended for clarity, correctness, and efficiency.

Approach	Time	Space	Stack Risk	Reconstruct	Use In Interview
1. Brute Force (Subquery with MAX and NOT IN)	O(n)	O(1)	No	N/A	Mention only - introduces problem
2. LIMIT and OFFSET	O(n log n)	O(1)	No	N/A	Mention if MySQL allowed, concise
3. DENSE_RANK() Window Function	O(n log n)	O(n)	No	N/A	Code this - optimal and clear
4. ROW_NUMBER() Window Function	O(n log n)	O(n)	No	N/A	Mention if unique ranking needed
5. DISTINCT + ORDER BY + LIMIT OFFSET	O(n log n)	O(n)	No	N/A	Mention for portability without window functions

💼

Interview Strategy

💡 Use this guide to understand multiple ways to solve the problem, their tradeoffs, and how to present them clearly in interviews.

How to Present

Step 1: Clarify the problem and ask about duplicates and SQL dialect.Step 2: Present the brute force subquery approach to show understanding.Step 3: Discuss LIMIT/OFFSET if MySQL is allowed.Step 4: Introduce window functions like DENSE_RANK for optimal solutions.Step 5: Mention DISTINCT with LIMIT/OFFSET for portability.Step 6: Code the preferred approach and test edge cases.

Time Allocation

Clarify: 2min → Approach: 5min → Code: 8min → Test: 5min. Total ~20min

What the Interviewer Tests

Your understanding of SQL ranking concepts, handling duplicates, knowledge of window functions, and ability to write clean, efficient queries.

Common Follow-ups

• What if you want the Nth highest salary? → Use window functions with rank = N
• How to handle ties differently? → Choose between ROW_NUMBER and DENSE_RANK

💡 These follow-ups test your ability to generalize and understand ranking semantics.

🔍

Pattern Recognition

When to Use

1) Need to find Nth highest or ranked value; 2) Table with numeric values; 3) Handle duplicates carefully; 4) SQL ranking or ordering required.

Signature Phrases

second highest salaryrank employees by salarytop N salaries

NOT This Pattern When

Problems that require aggregation without ranking or problems about cumulative sums are different patterns.

Similar Problems

Nth Highest Salary - generalizes to any NRank Employees by Salary - uses ranking functionsFind Top K Salaries - similar ordering and filtering

Practice

1. Given the following Python code simulating a JOIN to find customers with orders, what is the output of the print statement?

easy

A. ['Alice', 'Bob', 'Charlie']

B. ['Bob', 'Charlie']

C. ['Alice']

D. ['Alice', 'Bob']

Solution

1. Step 1: Trace order_customer_ids set

   Extract customer IDs from orders: {1, 2}.

2. Step 2: Filter customers by IDs in order_customer_ids

   Customers with id 1 and 2 are Alice and Bob; Charlie (id 3) is excluded.

3. Final Answer:

   Option D -> Option D

4. Quick Check:

   Only Alice and Bob have orders, matching output [OK]

Hint: Set membership filters customers with orders correctly [OK]

Common Mistakes:

• Including customers without orders
• Off-by-one errors in filtering
• Confusing customer IDs with order IDs

2. Which of the following statements about B+ Trees is INCORRECT?

medium

A. All keys appear in the leaf nodes, and internal nodes only store keys to guide searches.

B. Leaf nodes are linked sequentially to facilitate efficient range queries.

C. Data records can be stored in both internal and leaf nodes to speed up point queries.

D. Insertion and deletion operations may cause splitting or merging of leaf and internal nodes.

Solution

1. Step 1: Recall B+ Tree data storage rules

   Data records are stored only in leaf nodes; internal nodes store keys for navigation.

2. Step 2: Verify leaf node linkage

   Leaf nodes are linked sequentially to optimize range queries.

3. Step 3: Understand insertion/deletion effects

   Splitting and merging can occur in both leaf and internal nodes to maintain tree properties.

4. Step 4: Identify incorrect statement

   Data records can be stored in both internal and leaf nodes to speed up point queries. incorrectly states data records are stored in internal nodes, which contradicts B+ Tree design.

5. Final Answer:

   Option C -> Option C

6. Quick Check:

   Data only in leaves; internal nodes are index-only.

Hint: B+ Tree data only in leaves; internal nodes are keys only

Common Mistakes:

• Believing data can be stored in internal nodes
• Confusing B+ Tree with B-Tree data placement
• Ignoring leaf node linkage importance

3. Which of the following statements about clustered and non-clustered indexes is INCORRECT?

medium

A. Updating a non-clustered index key always requires physically moving the data row on disk.

B. Non-clustered indexes store a copy of the indexed columns and a pointer to the actual data row.

C. A table can have only one clustered index because it defines the physical order of rows.

D. Clustered indexes improve performance for range queries on the indexed column.

Solution

1. Step 1: Validate clustered index uniqueness

   Only one clustered index per table because it defines physical row order. Correct.

2. Step 2: Understand non-clustered index structure

   Non-clustered indexes store indexed columns plus pointers to data rows. Correct.

3. Step 3: Analyze update impact on non-clustered index keys

   Updating a non-clustered index key updates the index entry but does NOT require moving the physical data row unless clustered key changes.

4. Step 4: Confirm clustered index benefit for range queries

   Clustered indexes speed range queries by storing rows physically sorted. Correct.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Non-clustered index key update ≠ physical row move [OK]

Hint: Non-clustered index updates affect index entries, not physical row location [OK]

Common Mistakes:

• Confusing clustered and non-clustered index update effects
• Assuming multiple clustered indexes allowed
• Believing non-clustered indexes store full data rows

4. Why is it generally discouraged to directly implement many-to-many (N:M) relationships in a relational database without an associative (junction) table?

medium

A. Because direct N:M relationships cause data redundancy and complicate enforcing referential integrity.

B. Because relational databases do not support multiple foreign keys in a single table.

C. Because N:M relationships require storing duplicate primary keys in both tables.

D. Because N:M relationships can only be represented using triggers.

Solution

1. Step 1: Understand N:M relationship representation

   Direct N:M relationships are not supported as foreign keys in relational tables.

2. Step 2: Identify issues with direct implementation

   Trying to store multiple foreign keys in one table leads to redundancy and integrity problems.

3. Step 3: Use associative table to resolve

   Associative (junction) tables break N:M into two 1:N relationships, maintaining normalization and integrity.

4. Step 4: Evaluate other options

   Because relational databases do not support multiple foreign keys in a single table is false; tables can have multiple foreign keys. Because N:M relationships require storing duplicate primary keys in both tables is incorrect; primary keys are not duplicated but referenced. Because N:M relationships can only be represented using triggers is false; triggers are not required for N:M representation.

5. Final Answer:

   Option A -> Option A

6. Quick Check:

   Associative tables prevent redundancy and maintain referential integrity in N:M relationships.

Hint: N:M relationships require a junction table to avoid redundancy and maintain integrity.

Common Mistakes:

• Believing multiple foreign keys in one table can represent N:M directly
• Confusing primary key duplication with foreign key referencing

5. Why might using the Serializable isolation level in a high-throughput OLTP system be problematic despite its strong anomaly prevention guarantees?

medium

A. Serializable requires locking or versioning that can cause significant contention and reduce throughput

B. Serializable allows dirty reads, which can corrupt data

C. Serializable does not prevent phantom reads, so anomalies still occur

D. Serializable is the default isolation level in most databases, so it offers no advantage

Solution

1. Step 1: Understand Serializable Guarantees

   Serializable prevents all anomalies including dirty, non-repeatable, and phantom reads.

2. Step 2: Analyze Performance Impact

   To guarantee serializability, databases use strict locking or multi-version concurrency control with conflict detection, which can cause contention and reduce throughput.

3. Step 3: Evaluate Other Options

   Serializable allows dirty reads, which can corrupt data is false because Serializable prevents dirty reads. Serializable does not prevent phantom reads, so anomalies still occur is false because Serializable prevents phantom reads. Serializable is the default isolation level in most databases, so it offers no advantage is false; default levels vary.

4. Final Answer:

   Option A -> Option A

5. Quick Check:

   Serializable trades off performance for correctness, which can be costly in high-throughput systems.

Hint: Serializable = safest but slowest due to locking/contention

Common Mistakes:

• Believing Serializable allows dirty reads
• Thinking phantom reads occur at Serializable
• Assuming Serializable is default everywhere