一种方法是LIMIT一个相关子查询，但它不是特别有效：

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

除了埃格亚尔的精彩回答之外，下面这个查询还能给予薪酬与第三名相当的员工的姓名（在每个部门）：

SELECT 
    t.name, t.department, t.salary AS third_salary
FROM
    ( SELECT DISTINCT department 
      FROM tableX
    ) AS d
  JOIN
    tableX AS t
      ON  t.department = d.department 
      AND t.salary = 
      ( SELECT tt.salary                         -- notice that this
        FROM tableX AS tt                        -- subquery is
        WHERE tt.department = d.department       -- exactly the same as
        ORDER BY tt.salary DESC                  -- the one in 
          LIMIT 1 OFFSET 2                       -- @eggyal's answer
      ) ;

此RANK问题与以下问题类似：MySQL, Get users rank
我可以告诉你：

SELECT  s.*,
        (
        SELECT  COUNT(*)
        FROM    salaries si
        WHERE   si.salary >= s.salary AND si.department = s.department
        ) AS rank
FROM    salaries s
WHERE s.rank = 3

试试这个：

SELECT name, department, salary
FROM (SELECT name, department, salary, IF(@dept=(@dept:=department), @auto:=@auto+1, @auto:=1) indx 
      FROM employee e, (SELECT @dept:=0, @auto:=1) A
      ORDER BY department, salary DESC ) AS A 
WHERE indx = 3;

试试这个简单而优雅的解决方案：

-- get second and third highest employee based on salary from all departments
WITH rankedemployee AS (
    SELECT
        *,
        DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS denserank
    FROM employee
)
SELECT * FROM rankedemployee WHERE denserank IN (2,3);