CAML Query OrderBy ignoring non set values

Question

I'm currently writing some CAML Query for a QueryOverride in one of my Content Query Web Parts as I'm in need of multiple "Sort by" fields.

My issue is that one of the additional fields that I need sorting on may contain null values which make the query ignore the rows which has a null value and only performs the sort on rows which has a legitimate value.

My CAML Query so far

<OrderBy>
    <FieldRef Name="ArticleStartDate" Ascending="False" />
    <FieldRef Name="ArticlePriority" Ascending="True" />
</OrderBy>
<Where>
    <And>
        <Contains>
            <FieldRef Name="FilterMM" />
            <Value Type="TaxonomyFieldType">Company news</Value>
        </Contains>
        <And>
            <Leq>
                <FieldRef Name="ArticleStartDate" />
                <Value Type="DateTime"><Today /></Value>
            </Leq>
            <Geq>
                <FieldRef Name="_EndDate" />
                <Value Type="DateTime"><Today /></Value>
            </Geq>
        </And>
    </And>
</Where>

ArticlePriority is the troublemaker, as not all news have a priority set as they are less important than others which has priority 1,2,3 etc. meaning they should at all times be AFTER the ones with set priorities.

I tried adding Nullable="True" to it as I thought that might help but no luck.

Any ideas?

Answer 1

If ArticlePriority is a choice column then null values will be sorted before the ones with values.

One solution could be to create a hidden calculated column with the formular something like =IF(ArticlePriority=0,9999,ArticlePriority) and then order by that column