Given a table 'employees' with columns id, name, salary, department, write a query to find the third highest salary.

Question

Assisting AI · Accepted Answer

A common way to find the third highest salary is to use a subquery that selects distinct salaries ordered descending and limits the result to the third row. In SQL Server you can write:

```sql
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;
```

In MySQL you would use `LIMIT 2,1`:

```sql
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 2,1;
```

Alternatively, you can use a correlated subquery that counts how many distinct salaries are greater than the current one:

```sql
SELECT e1.salary
FROM employees e1
WHERE (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
) = 2;
```

All three approaches return the third highest unique salary. The first two use set-based ordering and offset/limit, which are efficient on indexed columns. The third uses a correlated subquery and may be slower on large tables but is more portable across database engines.

Given a table 'employees' with columns id, name, salary, department, write a query to find the third highest salary.

💡 Model Answer

🎤 Get questions like this answered in real-time