一种方法是 union all :

select t.*
from t
where t.status = 'King'
union all
select t.*
from t
where t.status = 'Queen' and
      not exists (select 1 from t t2 where t2.name = t.name andt2.status in ('King'))
union all
select t.*
from t
where t.status = 'Jack' and
      not exists (select 1 from t t2 where t2.name = t.name and t2.status in ('King', 'Queen'));

另一种方法使用相关子查询：

select t.*
from t
where (name, field(t.status, 'King', 'Queen', 'Jack')) in
          (select t2.name, max(field(t2.status, 'King', 'Queen', 'Jack'))
           from t t2
           where t2.status in ('King', 'Queen', 'Jack')
           group by t2.name
          );

尝试使用会话变量：

SET @row_number:=1;

SELECT
    FirstName,
    LastName,
    Number,
    Status
FROM(
    SELECT 
        @row_number:=
        CASE
            WHEN @FirstName = FirstName AND @LastName = LastName AND @Number = Number
            THEN @row_number + 1
            ELSE 1
        END AS num,
        @FirstName := FirstName as FirstName,
        @LastName := LastName as LastName,
        @Number := Number as Number,
        Status
    FROM
        t1
    ORDER BY 
        FirstName,
        lastName,
        Number,
        CASE 
            WHEN STATUS = 'King' THEN '1'
            WHEN STATUS = 'Queen' THEN '2'
            WHEN STATUS = 'Jack' THEN '3'
        END
    ) as ttt
WHERE num = 1;