Let's say I am returning the following table from a select

I want to select ONLY the row with DocumentType = 'I797', then if there is no 'I797', I want to select ONLY the row where DocumentType = 'I94'; failing to find either of those two I want to take all rows with any other value of DocumentType.

Using SQL Server ideally.

I think I'm looking for an XOR clause but can't work out how to do that in SQL Server or to get all other values.

CodePudding user response：

Similar to @siggemannen answer

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

CodePudding user response：

Something like this perhaps:

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

The idea is to rank rows by 1, 2, 3 depending on document type. Since we rank "the rest" the same, you will get all rows if I797 and I94 is missing.

CodePudding user response：

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' ))