Show TOP 1 considering where condition if present -SQL query

Question

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 1

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