+ Start a Discussion
garybgaryb 

Picklist values and SOQL query sort order.

You have an object with a picklist that contains three values, x, y and z. For testing purposes, you create a record of that object type with a value of "a" in the picklist - Apex allows you to do this. There are already records in the database withvalues of x, y or z in the picklist.

 

You then have a query (after the test has created the record with the "a" value in the picklist) that runs like this:

 

 

Some_Object__c so = [select ID from Some_Object__c order by Your_Picklist_field__c asc nulls last limit 1];

 

 

Which record is returned from the database? The one with "a"? No - for some reason this is the last record returned by the query.

 

Can anyone else confirm this, and if there's anything obvious I'm missing to get what I think should be the expected result - the value inserted by the test is the returned record?

Best Answer chosen by Admin (Salesforce Developers) 
mtbclimbermtbclimber

This tripped me up when I was first using order by in SOQL too. The way sorting works with picklists is that the order is determined by the order of the picklist values as defined on the field in setup and NOT alphabetically as though they were simply text.

 

When you insert a record through the API or Apex with a different value it creates what is called an "Inactive Picklist Value" which are treated as though they were simply appended to the end of the list in terms of ordering but don't show up in the UI for users to select, only "Active" values show up in that way.  I believe if you switch your query to order DESC you'd get the result you are after.

All Answers

mtbclimbermtbclimber

This tripped me up when I was first using order by in SOQL too. The way sorting works with picklists is that the order is determined by the order of the picklist values as defined on the field in setup and NOT alphabetically as though they were simply text.

 

When you insert a record through the API or Apex with a different value it creates what is called an "Inactive Picklist Value" which are treated as though they were simply appended to the end of the list in terms of ordering but don't show up in the UI for users to select, only "Active" values show up in that way.  I believe if you switch your query to order DESC you'd get the result you are after.

This was selected as the best answer
garybgaryb

Thanks for confirming :)

 

I could use DESC, however, I still want x, y and z to be ordered alphabetically and though DESC will solve my problem with "a", it will obviously return x, y and z results in the wrong order.

 

Not to worry, I can work around it for now. Thanks again!

mtbclimbermtbclimber

Sure thing.

 

For those coming across this later who may curious this is intentional.  In addition to picklists being order defined and wanting to reflect that this approach also keeps result ordering consistent across translations. For example values x, y, and z may translate in French to c, b, and q.

 

The only suggestion I can make if you want the data ordered alphabetically and you need to provide inactive values that fall in that order is to not use a picklist (for sorting anyway). If your users are all using the same language (or your values aren't translated) you could copy the values from picklist to a text field with a workflow rule + field update and then sort on that field. If you do require translations and all your users are expecting the data to be ordered alphabetically then this solution is not going to be sufficient.

 

Keep in mind also that with this approach the values would then be de-coupled and a picklist value change could get things out of sync.

 

Hope that helps.

mlegrandmlegrand

Hi Andrew,

 

 

I am not able to use 'NULLS LAST' while sorting the data by a picklist field and it is a blocking point for my project.

The SOQL request  above does not return result with <picklist_field> equals NULL in last position :

 

Select <Contact_Fields> from Contact Order by <picklist_field> NULLS LAST

 

I didn't find any documentation about this limitation

Another forum member posted same issue: http://boards.developerforce.com/t5/General-Development/Nulls-Last-in-Picklist-field/td-p/83973

 

Which workaround are you suggesting ?

 

Thank you in advance for your answer

Regards