In Structured Query Language SQL, which query pattern correctly returns the third highest distinct salary from an employee table named Employees with a Salary column?

Introduction / Context:
Interviewers frequently ask candidates how to retrieve the nth highest salary from a table using Structured Query Language. This tests understanding of ordering, distinct values, and row limiting clauses. Retrieving the third highest salary requires excluding duplicate salaries and selecting the correct row from an ordered result set. This question presents several possible query patterns and asks you to choose the one that correctly returns the third highest distinct salary from a table named Employees with a Salary column.

Given Data / Assumptions:

• The table is named Employees and has at least one numeric column called Salary.
• There may be duplicate salary values in the table.
• The database supports ORDER BY with OFFSET and FETCH or an equivalent row limiting mechanism.
• The goal is to retrieve exactly one value that represents the third highest distinct salary.

Concept / Approach:
To find the third highest distinct salary, first remove duplicates with DISTINCT. Then sort the salaries in descending order so that the highest salary appears first, the second highest second, and so on. Once ordered, skip the first two rows and return the next row. In SQL dialects that support OFFSET and FETCH, this can be expressed as ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY. This pattern ensures that the result contains a single row representing the third highest distinct salary. Alternative approaches may use subqueries or analytic functions, but the key idea is consistent: order salaries and select the third distinct value.

Step-by-Step Solution:
Step 1: Apply DISTINCT to Salary to ensure that repeated salary values are treated as one when determining the ranking. Step 2: Use ORDER BY Salary DESC to sort distinct salaries from highest to lowest. Step 3: Use OFFSET 2 ROWS to skip the first two distinct salaries, which correspond to the highest and second highest. Step 4: Use FETCH NEXT 1 ROW ONLY to retrieve the next row, which is the third highest distinct salary. Step 5: Compare this pattern with the options and recognise that option a implements exactly these steps.

Verification / Alternative check:
Consider sample salaries: 90, 80, 80, 70, 60. Applying SELECT DISTINCT Salary gives 90, 80, 70, 60. Ordering in descending order yields 90, 80, 70, 60. Skipping two rows gives 70, which is indeed the third highest distinct salary. The query in option a would return 70 in this scenario. Other query patterns either ignore distinctness or select multiple rows, demonstrating that option a is the precise answer for this requirement.

Why Other Options Are Wrong:
Option b selects the top three salaries in ascending order, which returns three rows and not necessarily the third highest distinct salary; it may include duplicates and the lowest salaries instead of the highest. Option c simply returns the minimum salary, which is unrelated to the third highest salary. Option d uses a syntactically incorrect condition and does not perform any ordering or row limiting, so it cannot produce the desired result.

Common Pitfalls:
A common mistake is to ignore distinctness and simply select the third row from an ordered list, which fails when duplicate salaries exist. Another pitfall is using TOP 3 and then expecting to identify the third highest without an additional layer of ordering around that subset. For more advanced database engines, analytic functions such as DENSE_RANK can also solve this problem elegantly, but exams often expect an understanding of DISTINCT, ORDER BY, and row limiting as shown in option a.

Final Answer:
A query that correctly returns the third highest distinct salary is SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY; as given in option a.