Assume you have an employees table. Write a query to fetch the second highest salary.

To retrieve the second highest salary from an employees table, you can use a subquery that selects distinct salaries in descending order and limits the result to the second row. One common approach is:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

This query orders all distinct salaries from highest to lowest, skips the first row (the highest salary) with OFFSET 1, and returns the next row. It works in databases that support LIMIT/OFFSET such as MySQL, PostgreSQL, and SQLite.

If your database does not support LIMIT/OFFSET (e.g., SQL Server), you can use a window function:

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk = 2;

Here, DENSE_RANK assigns 1 to the highest salary, 2 to the second highest, and so on. The outer query filters for rank 2.

Both methods run in O(n) time, where n is the number of rows in employees, because they require a single scan and sort. They also use O(1) additional space beyond the result set. This solution is efficient and works across most relational databases.