Show TOP 1 considering where condition if present -SQL query

I have a case where I am using a OUTER APPLY query as below

OUTER APPLY (
    SELECT TOP 1 CUSTOMER_CATEGORY 
    FROM   [UX_VW_CUSTOMER_DETAILS] UVFS
    WHERE  UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
) SFD

But I have new requirement where OUTER APPLY should happen based on considering customer_category = 'General' if present.

Pseudo code will be like as below

if (Any Item present in [UX_VW_CUSTOMER_DETAILS] with CUSTOMER_CATEGORY=="General' for the specific customer)
{
    SELECT TOP 1 CUSTOMER_CATEGORY 
    FROM   [UX_VW_CUSTOMER_DETAILS] UVFS
    WHERE  UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
    AND UVFS.CUSTOMER_CATEGORY LIKE '%General%'
}
ELSE
{
    SELECT TOP 1 CUSTOMER_CATEGORY 
    FROM   [UX_VW_CUSTOMER_DETAILS] UVFS
    WHERE  UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
}

Can anyone suggest better way to rewrite outer apply code in efficient way.

Answer

You can combine your conditions by adding an order by clause to your outer apply query to prioritise CUSTOMER_CATEGORY = 'General' e.g.

select top 1 CUSTOMER_CATEGORY
from [UX_VW_CUSTOMER_DETAILS] UVFS
where UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
order by case when UVFS.CUSTOMER_CATEGORY like '%General%' then 1 else 0 end desc

The result of the case expression when CUSTOMER_CATEGORY like '%General%' is 1 and 0 otherwise. We then order by the result of the case expression in a descending manner i.e. highest to lowest. In summary this means that if the CUSTOMER_CATEGORY like '%General%' it will select selected as a priority.

To further understand how this works consider the results produced by:

declare @Id int = 1; -- Choose a Customer ID to test with

select *
    , case when UVFS.CUSTOMER_CATEGORY like '%General%' then 1 else 0 end desc OrderBy
from [UX_VW_CUSTOMER_DETAILS] UVFS
where UVFS.CUSTOMER_ID = @Id
order by case when UVFS.CUSTOMER_CATEGORY like '%General%' then 1 else 0 end desc