Find 3rd Greatest Value with MySQL GROUP BY

Question

I have a table with 3 columns:

Name department salary

How can I determine using one query to find 3rd highest salary in each department?

Answer 1

One way is to LIMIT a correlated subquery, but it's not especially efficient:

SELECT   department, (
  SELECT   salary
  FROM     my_table t2
  WHERE    t2.department = t1.department
  ORDER BY salary DESC
  LIMIT    2, 1
)
FROM     my_table t1
GROUP BY department