OFFSET Clause in SOQL (Salesforce Object Query Language)

As a Salesforce Developer, you want to retrieve records from the Salesforce object, and the results should display like each page of the portal displays 10 records, and we can navigate through pages to view more results using SOQL in Apex. For that, we have different clauses that have the functionality to fetch records in different patterns as per our requirements.

In this Salesforce tutorial, we will learn about the OFFSET Clause in SOQL (Salesforce Object Query Language). In that, I will explain the functionality of the OFFSET clause and how to use it in SOQL queries with different examples.

Table of Contents

What is the OFFSET Clause in SOQL?

When we expect many records to be retrieved in query results, we can display the results on multiple pages by using the OFFSET clause in a SOQL query. We can use the OFFSET keyword in SOQL to specify the starting row of the retrieved result. For example, if there are 50 records, then if we specify offset as 20 in the query, it would return records 21 to 50; it will skip the first 20 records.

The OFFSET clause in a SOQL is used to skip a specified number of rows or records from the beginning of the query results. This is particularly useful for implementing pagination when retrieving data from Salesforce, allowing us to control which subset of records to fetch in each query. It helps to implement pagination in your queries when dealing with large datasets.

OFFSET has a maximum limit of 2,000 rows. This means that we cannot skip more than 2,000 records. OFFSET must be used with an ORDER BY clause to ensure the rows are returned in a predictable order.

Syntax: Declaring OFFSET Clause in SOQL

SELECT fields FROM object_name WHERE conditions ORDER BY field OFFSET number_of_rows_to_skip

SELECT: The SELECT clause specifies the fields to retrieve in the query results.
FROM: The FROM clause defines the Sobject from which to retrieve data.
WHERE (Optional): The WHERE clause filters the query results based on specific conditions, as you specify.
ORDER BY: Using the ORDER BY clause, we can sort results in ascending or descending order or in null-first or null-last order.
OFFSET: In the OFFSET clause, we need to specify the number of rows or records to skip.

We can also use a LIMIT clause with OFFSET if we need to retrieve subsequent subsets of the same result set.

Example: Use the OFFSET Clause in SOQL Query

In the steps below, I have explained the SOQL query using the OFFSET clause so that we can display the results on multiple pages.

For example, first, I want to display 10 lead records on each page, like the first 10 records on page 1, and the next 11 to 20 on page 2. So, to skip the first 10 records, then fetch and display leads from record no 11-20 on page 2. We will use the OFFSET clause in SOQL.

Page 1: Display the First 10 records:

SELECT Name, LeadSource FROM Lead ORDER BY Name LIMIT 10 OFFSET 0

In the above SOQL query, we are retrieving lead records using ORDER BY names, and when we use the ORDER BY clause, the records will be displayed in ascending order in the query result. After that, we used the LIMIT clause in SOQL, which displays only the number of records we provided.

Here, in the OFFSET clause, we provided 0 because we want to display the first 10 records, and we don’t want to skip any record.

Page 2: Display 11-20 records.

SELECT Name, LeadSource FROM Lead ORDER BY Name LIMIT 10 OFFSET 10

In this query, we added OFFSET 10 because on the first page, we already displayed the first 10 records, and now we don’t want to display those records again. For that, we need to skip the first 10 records and display the next 11-20 records.

Like this, we can create multiple pages, and using the OFFSET clause, we can skip records so that we can display the results on multiple pages.

Example: Use WHERE Clause With OFFSET Clause in SOQL

Now, we will understand how to use conditions to filter and display the query result as per our requirements. In the steps below, I have explained how to use the WHERE clause with the OFFSET clause in SOQL.

For example, we want to retrieve all account records whose account priority is high, and the query result should display 10 records on the first page, then another 10 records on another page, and so on.

When we display 10 records on the first page, we need to skip the first 10 records that we displayed on the first page while displaying the next 10 records on the second page. For that, we need to use the OFFSET clause in SOQL.

Page 1: Display the First 10 records:

SELECT Id, Name, Account_Priority__c FROM Account WHERE Account_Priority__c = 'High' ORDER BY Id LIMIT 10 OFFSET 0

So, in the above SOQL query, we added a condition to display high-priority accounts. For that, we used the WHERE clause to add a condition. Then, using the LIMIT clause, we displayed a limited number (10 records) in the query result.

Then, in the OFFSET clause, we provided zero as a value because here, we want to display the result from the first record.

OFFSET Clause in SOQL (Salesforce Object Query Language)

Page 2: Display the Next 10 records:

Now, here, we want to display the next 10 records on the second page. For that, we need to skip the number (10 records) of records that we displayed on the first page. To skip the records, we need to provide 10 values to the offset clause.

SELECT Id, Name, Account_Priority__c FROM Account WHERE Account_Priority__c = 'High' ORDER BY Id LIMIT 10 OFFSET 10

Page 3: Display the Next 10 records:

Now, to display the next 10 records in offset, we need to provide the number of records that we displayed on all the previous pages. So here we provided 20.

SELECT Id, Name, Account_Priority__c FROM Account WHERE Account_Priority__c = 'High' ORDER BY Id LIMIT 10 OFFSET 20

As we execute the above query in the result, you can see only 8 results displayed. However, in the limit clause, we provided 10 results to display. This is because in the Salesforce database, there were only 8 records remaining, and for that, it displayed only those 8 records.

In this way, we can use the OFFSET clause in SOQL to skip records so that we can display the results on multiple pages.

Conclusion

I hope you have got an idea about the OFFSET Clause in SOQL (Salesforce Object Query Language). In that, I have explained the functionality of the OFFSET clause and how to use the OFFSET clause in SOQL queries with different examples.