类似于@siggemannen答案

select top 1 with ties
    case when DocumentType='I797' then 1
         when DocumentType='I94' then 2
    else 3
    end gr
   ,docs.*
from docs
order by 
    case when DocumentType='I797' then 1
         when DocumentType='I94' then 2
    else 3
    end

最短：

select top 1 with ties
   docs.*
from docs
order by 
    case when DocumentType='I797' then 1
         when DocumentType='I94' then 2
    else 3
    end

大概是这样的：

select *
from (
    select t.*, DENSE_RANK() OVER(ORDER BY CASE WHEN DocumentType = 'I797' THEN 0 WHEN DocumentType = 'I94' THEN 1 ELSE 2 END) AS prioorder
    from 
    (
        VALUES  
            (1, 1, 1, N'I797', N'01/02/23')
        ,   (1, 2, 2, N'I94', N'01/02/23')
        ,   (1, 3, 3, N'Some Other Value', N'01/02/23')
        ,   (1, 4, 3, N'Super Sekret', N'01/02/23')
    ) t (CaseId,DocId,DocumentTypeId,DocumentType,ExpirationDate)
    ) x
WHERE   x.prioorder = 1

其思想是根据文档类型按1、2、3对行进行排序。由于我们对“其余行”的排序相同，因此如果I797和I94缺失，您将获得所有行。

select * from YourTable where DocumentType = 'I797'
union 
select * from YourTable t where DocumentType = 'I94' and (not exists (select * from YourTable where DocumentType = 'I797'))
union
select * from YourTable t where (not exists (select * from YourTable where DocumentType = 'I797' or DocumentType = 'I94' ))