Paging with LIMIT and last-value

SOQL queries · Intermediate · 15 min · Runs in your Salesforce Developer org · Apex Path

Page through four tagged Accounts two at a time. Order by Name, LIMIT 2, then start the next page where the last one stopped with `Name > :lastName`. The point is to see why last-value paging keeps working past 2000 rows while OFFSET does not.

Lesson

Why this matters

A report that lists every Account cannot pull them in one query: SOQL caps a single call at 50,000 rows, and anything bigger times out anyway. Paging solves that by fetching a page, remembering where you stopped, and asking the next page to start there. OFFSET works for small jumps, but SOQL caps OFFSET at 2000, so it fails on real datasets. Keyset paging ('last-value') keeps working.

The smallest working example

List<Account> page = [
    SELECT Id, Name FROM Account
    WHERE Name LIKE :pattern
    ORDER BY Name ASC
    LIMIT 2
];
String lastName = page[page.size() - 1].Name;

List<Account> next = [
    SELECT Id, Name FROM Account
    WHERE Name LIKE :pattern AND Name > :lastName
    ORDER BY Name ASC
    LIMIT 2
];

The second query asks for rows whose Name sorts after lastName, in the same sort order. No row is skipped and no row is repeated.

Name the parts

**ORDER BY Name ASC** is the sort that makes paging stable. Without it, the second page is undefined.
**LIMIT 2** caps each page.
**Name > :lastName** is the keyset filter. Use > (not >=) so the row you already saw is skipped.
**:pattern** scopes to this run's tagged Accounts so the queries never see unrelated rows.

A common wrong version

List<Account> next = [
    SELECT Id, Name FROM Account
    WHERE Name LIKE :pattern AND Name >= :lastName
    ORDER BY Name ASC
    LIMIT 2
];

Using >= includes the row you just saw. The second page starts with a duplicate.

The fix

Sort both queries the same way, cap each page with LIMIT, and open the next page with Name > :lastName.

Now you try

The starter seeds four Accounts (<tag>-01 to <tag>-04) in one list. Build pattern = tag + '%'. Query page 1, remember lastName, query page 2, then print Page 1 size:, Page 1 names: (comma-joined), Page 2 size:, Page 2 names: and Combined size: in that order.

Learn more

The challenge

Take four tagged Accounts (-01 to -04) and fetch them in two pages of two.

Examples:

first page → Page 1 names: <tag>-01,<tag>-02
second page, after <tag>-02 → Page 2 names: <tag>-03,<tag>-04
each page returns 2 rows

Requirements:

Name > :lastName should be present on the second query, not OFFSET.
ORDER BY Name ASC should be present on each query.
Name LIKE :pattern should be present on each query.
Page 1 size: 2 should be printed from the first page.
Page 2 size: 2 should be printed from the second page.
Combined size: 4 should be printed from both page sizes added.
Page 1 names: should be -01, then -02.
Page 2 names: should be -03, then -04.

Starter code

String tag = 'AS-Q320-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
List<Account> seeded = new List<Account>{
    new Account(Name = tag + '-01'),
    new Account(Name = tag + '-02'),
    new Account(Name = tag + '-03'),
    new Account(Name = tag + '-04')
};
insert seeded;

System.debug('Page 1 size: 0');
System.debug('Page 1 names: ');
System.debug('Page 2 size: 0');
System.debug('Page 2 names: ');
System.debug('Combined size: 0');

Related drills

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Multi-level relationships with __r