4

I have an aggregate query on the Lead object. The query returns a list of Leads grouped by website (custom field) in a specific order based on a MAX of a date attribute.

Say once we iterate through the List, the ordered list of websites is ["websiteB", "websiteF", "websiteA"].

What if I want to re-query against the Lead object, and order the leads by this list of websites?

In mySQL, I could do an ORDER BY FIELD(..., ..., ..), or perhaps a case-when (see here: https://stackoverflow.com/questions/1244028/sql-order-by-list-of-strings). Any ideas in SOQL?

2

In SOQL you need to add a formula field called e.g. WebsiteOrder__c to hold the case expression:

CASE(Website__c,
"websiteB", 100,
"websiteF", 200,
"websiteA", 300,
1000
)

and then just order by that field.

| improve this answer | |
0

You can use Order By in SOQL as well, and you can have Order By multiple fields.

list<Contact> cons = [Select Id, Name From Contact Order By LastName, FirstName, CreatedDate];

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_orderby.htm

| improve this answer | |
  • We only want to ORDER BY one field; website. And that ordering would be defined by the list of strings I stated (["websiteB", "websiteF", "websiteA"]), it wouldn't be alphabetic. For example, this would be a valid result I want: 1: Lead.website = websiteB 2: Lead.website = websiteB 3: Lead.website = websiteF 4: Lead.website = websiteF 5: Lead.website = websiteA – John Oct 19 '14 at 0:43

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.