Mytutorialrack

Menu
Youtube Linkedin-in Facebook Twitter
Order by IN SOQL

In Salesforce Object Query Language (SOQL), the ORDER BY clause is used to sort query results in a specific order. With “ORDER BY” in SOQL, you can sort records based on fields such as date, name, or any other relevant criteria. Here’s how the ORDER BY clause is used in SOQL:

SELECT field1, field2, ...
FROM sObject
WHERE condition
ORDER BY field1 [ASC|DESC]

Explanation

  • SELECT: Specifies the fields to retrieve in the query results.
  • FROM: Specifies the object on which the query is performed.
  • WHERE: (Optional) Specifies any conditions to filter the query results.
  • ORDER BY: Specifies the field to sort the query results and the order (ASC or DESC)

Example:

Let’s assume we query “Opportunity” object with fields like “Name,” “Amount,” and “CloseDate.” To retrieve and sort the opportunities by their amounts in descending order, the SOQL query would be:

SELECT Name, Amount, CloseDate
FROM Opportunity
ORDER BY Amount DESC

Sorting Order: The ORDER BY clause supports both ascending (ASC) and descending (DESC) sorting orders. By default, if no sorting order is specified, it will be ascending. You can explicitly specify the sorting order for the field in the ORDER BY clause.

SELECT Name, Amount, CloseDate
FROM Opportunity
ORDER BY Amount ASC

“NULLS FIRST” or “NULLS LAST”

In Salesforce Object Query Language (SOQL), you can control the placement of null values in the query results using the keywords “NULLS FIRST” or “NULLS LAST” within the ORDER BY clause. These keywords allow you to specify whether null values should appear at the beginning or end of the sorted results. Here’s how they work:

NULLS FIRST: When you use the “NULLS FIRST” keyword in the ORDER BY clause, null values are positioned at the beginning of the sorted results. The non-null values are then sorted based on the specified sorting order (ASC or DESC). This means that null values take precedence over non-null values in the sorting order.

SELECT Name, AnnualRevenue
FROM Account
ORDER BY AnnualRevenue ASC NULLS FIRST

NULLS LAST: Conversely, when you use the “NULLS LAST” keyword, null values are positioned at the end of the sorted results. The non-null values are sorted first, and the null values appear after them. In this case, non-null values take precedence over null values in the sorting order.

SELECT Name, AnnualRevenue
FROM Account
ORDER BY AnnualRevenue ASC NULLS LAST

It’s important to note that the “NULLS FIRST” and “NULLS LAST” keywords are optional and only applicable when sorting fields that can contain null values. By default, if you don’t explicitly specify either keyword, null values are sorted according to the default behavior of the sorting order (ASC or DESC).

Limitations on ORDER BY clause In SOQL

When using the ORDER BY clause in Salesforce Object Query Language (SOQL), there are certain limitations and considerations related to specific data types. Here are the limitations for data types when using ORDER BY:

  • Unsupported Data Types: Certain data types are not supported for sorting using the ORDER BY clause. These include multi-select picklist, rich text area, long text area, encrypted fields (if enabled), and data category group reference (if Salesforce Knowledge is enabled). Sorting is not possible directly on these data types.
  • Corporate Currency: For currency fields, such as the corporate currency, sorting is based on the currency value if available. It uses the numeric value of the currency field for sorting purposes.
  • Phone Fields: Phone fields do not undergo any special formatting when sorting. Non-numeric characters like dashes or parentheses are included in the sorting order.
  • Picklist Sorting: The sorting order for picklist fields is determined during setup and is defined by the picklist sort configuration. The values are sorted according to the predefined order set up for the picklist field.

Limitations for ORDER BY in SOQL for External Objects

When working with external objects, there are additional limitations for the ORDER BY clause:

  • For external objects using the OData 2.0 and 4.0 adapters in Salesforce Connect, the NULLS FIRST and NULLS LAST keywords are ignored.
  • External objects do not support the ORDER BY clause in relationship queries.
  • Similar to external objects,, the NULLS FIRST and NULLS LAST keywords are ignored in custom adapters for Salesforce Connect.

These limitations and considerations help ensure that you use the ORDER BY clause effectively and within the supported capabilities of Salesforce. It’s important to be aware of these limitations when designing your queries and handling specific data types in your sorting requirements.

You can use the ORDER BY clause with the optional LIMIT qualifier in a SELECT statement.

SELECT Name, AnnualRevenue
FROM Account 
ORDER BY AnnualRevenue ASC NULLS LAST LIMIT 10


Platform Developer 1 Certification

Share:

Recent Posts