ORDER BY with a specific value first

Question

How do I get --ALL-- to be at the top of my order by? I cannot use CASE WHEN in the ORDER BY clause because of the UNION. I also think the IF/ELSE is a complication here.

This code lives in an sp that I didn't write originally. I was asked to duplicate it and add a null value (the original script didn't have any nulls).

My solution was to UNION in

SELECT  
    NULL as parameterValue,
    CAST('--All--' as VARCHAR(50)) AS parameterLabel

Now I need that Null, --All-- row to always be at the top of my results and the rest of the results to be parameterValue desc

I want to make this solution work, but I cannot figure out how to with the IF/Else: Order by a specific value first then show all rest in order

DECLARE 
    @MaxCampaignYear int,
    @rptSection nvarchar(85) = 'Main',
    @showAll bit = 0

    SELECT 
        @MaxCampaignYear = MAX(campaignYearNum) 
    FROM 
        dbo.dimDate
    IF @rptSection = 'Main'
        SELECT
            NULL as parameterValue,
            CAST('--All--' as VARCHAR(50)) AS parameterLabel
        UNION ALL
        SELECT DISTINCT
            campaignYearNum AS parameterValue,
            CAST(campaignYearNum as VARCHAR(50))  AS parameterLabel
        FROM
            dbo.dimDate
        WHERE
            (
            @showAll <> 0
            or campaignYearNum <= @MaxCampaignYear --BI_DW.dbo.udfGetCampaignYear(getdate())
            )
            and campaignYearNum > 0
        ORDER BY
            parameterValue desc
    ELSE
        SELECT
            BI_DW.dbo.udfGetCampaignYear(getdate()) as parameterValue,
            BI_DW.dbo.udfGetCampaignYear(getdate()) as parameterLabel```

user_5714791 · Answer

You could add a RowNum identifier in each of your unions, and then order by RowNum asc, parameterValue desc, this way you will always get --All-- first, then parameterValue desc.

SELECT DISTINCT
            RowNum = 1,
            NULL as parameterValue,
            CAST('--All--' as VARCHAR(50)) AS parameterLabel
        UNION ALL
            RowNum = 2,
            campaignYearNum AS parameterValue,
            CAST(campaignYearNum as VARCHAR(50))  AS parameterLabel
        FROM
            dbo.dimDate
        WHERE
            (
            @showAll <> 0
            or campaignYearNum <= @MaxCampaignYear --BI_DW.dbo.udfGetCampaignYear(getdate())
            )
            and campaignYearNum > 0
        ORDER BY
            RowNum asc, parameterValue desc

user_74195 · Answer

To float your ALL to the top, update the order by to something like

order by
   case when '--All--' = parameterLabel
        then 1 else 2 end,
   parameterLabel

So, the first sort gets ALL floated to the top, THEN everything else after that.

ORDER BY with a specific value first

2 Answers