SOQL Nulls Last with Orderby Picklist field doesn't work

Question

I have the following query -

[SELECT Id, ContactId, LeadId, Status, Priority_c, CampaignId, Lead.Name, Contact.Name, Campaign.Name FROM CampaignMember WHERE CampaignId = :stdController.getRecord().Id ORDER BY Status DESC, Priority_c ASC NULLS LAST limit 1000];

Problem is that the NULLS LAST, does not work! I am getting null results appearing before my ordered results. I am required to have the null values required last so it is looking like I will need to implement my own custom sorting. Or does someone have any other workaround?

Answer 1

Sorting on picklist fields is a bit of a special case. Typically

"picklist sorting is defined by the picklist sort determined during setup." - ORDER BY Docs

You can refine this using the Reorder button on the picklist field.

However, NULLS LAST doesn't appear to be respected on picklists. See (and upvote) - Implement SOQL Order By NULLS LAST. It appears to be a long standing issue.

One possible workaround would be to create a text field that you populate with a workflow rule and field update. This would allow you to sort alphabetically and should respect null values. Note that you could get into issues with this if you multiple languages/translations (which I think is why picklists have sort orders).

Another thing to watch out for could be what are called "inactive picklist values". This is where you have assigned a value to the picklist that isn't in the current list of allowed values. These should appear after the defined values in sorting.

Answer 2

You can try creating a text type formula field, with the name PriorityText__c like this: TEXT(Priority__c)

Then you change your Query for this: SELECT Id, ContactId, LeadId, Status, Priority_c, CampaignId, Lead.Name, Contact.Name, Campaign.Name FROM CampaignMember WHERE CampaignId = :stdController.getRecord().Id ORDER BY Status DESC, PriorityText__c ASC NULLS LAST limit 1000