Custom Sort Order in CAML Query

Question

How would one go about telling a CAML query to sort the results in a thoroughly custom order?

.

For instance, for a given field:

-- when equal to 'Chestnut' at the top,

-- then equal to 'Zebra' next,

-- then equaling 'House'?

Finally, within those groupings, sort on a second condition (such as 'Name'), normally ascending.

So this

    ID   Owns       Name
    ————————————————————
    1    Zebra      Sue
    2    House      Jim
    3    Chestnut   Sid
    4    House      Ken
    5    Zebra      Bob
    6    Chestnut   Lou

becomes

    ID   Owns       Name
    ————————————————————
    6    Chestnut   Lou
    3    Chestnut   Sid
    5    Zebra      Bob
    1    Zebra      Sue
    2    House      Jim
    4    House      Ken

In SQL, this can be done with Case/When. But in CAML? Not so much!

Niels V Niels V · Accepted Answer · 2017-12-20T18:47:51

CAML does not have such a sort operator by my knowledge. The workaround might be that you add a calculated column to the list with a number datatype and formula =IF(Owns="Chestnut",0,IF(Owns="Zebra",1,IF(Owns="House",3,999))).

Now it is possible to order on the calculated column, which translates the custom sort order to numbers. Another solution is that you create a second list with the items to own, and a second column which contains their sort order. You can link these two lists and order by the item list sort order. The benefit is that a change in the sort order is as easy as editing the respective listitems.

Custom Sort Order in CAML Query

1 Answers