5

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?

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.