How to paginate > 10000 records. SOQL OFFSET and StandardSetController too limiting

  • I am working on pagination and i am able to display my records on my VF page and i have done pagination to get the record get display. But the pagination work only when the no of records is less than 2000. If the number of records are more than 2000 and when we click on the last button which show all the last records salesforce throws an error

    Maximum SOQL offset allowed is 2000 Error is in expression '{!endbtn}' in component <apex:commandButton> in page accountpagination_vf. An unexpected error has occurred. Your development organization has been notified.

    I understand why this issue is getting its because salesforce have limit of 2000 on records for pagination. But mu client have more than 2000 records and he want all to be get displayed on the VF page on pagination. Please guide me the best way to achieve this scenario. my code for VF page :

    <apex:page controller="accountPagination_ctrl">
     <apex:pageBlock >
      <apex:form >
          <apex:commandButton value="Search" action="{!searchresult}"/>
      <apex:dataTable value="{!accLst}" var="ac">
          <apex:column headerValue="Name">
              <apex:outputField value="{!ac.Name}"/>
          <apex:column headerValue="Id">
              <apex:outputField value="{!ac.Id}"/>
      <apex:commandButton value="FIRST" action="{!fstbtn}" disabled="{!prv}"/>
      <apex:commandButton value="PRE" action="{!prvbtn}" disabled="{!prv}"/>
      <apex:commandButton value="NXT" action="{!nextbtn}" disabled="{!nxt}"/>
      <apex:commandButton value="END" action="{!endbtn}" disabled="{!nxt}"/>

    controller :

    public class accountPagination_ctrl{
    public List<Contact> accLst {get; set;}
    public List<Contact> accLst_size {get; set;}
    public integer counter = 0;
    public integer recNum = 0;
    public integer limits = 5;
    public set<id> contactID {get; set;}
    public accountPagination_ctrl(){
        accLst = new List<Contact> ();
        accLst_size = new List<Contact> ();
        contactID = new set<id>();
    public void Result(){
        accLst_size = [SELECT Name, Id FROM Contact WHERE name != null];
        recNum = accLst_size.size();
    public void searchresult(){
        SYSTEM.DEBUG('Number of records ' + recNum ); 
        accLst = [SELECT Name, Id FROM Contact WHERE id in: contactID and name != null LIMIT :limits OFFSET: counter  ];
    public void nextbtn(){
        counter += limits ;
    public void prvbtn(){
        counter -= limits ;
    public void fstbtn(){
        counter = 0;
    public void endbtn(){
        counter = recNum - math.mod(recNum,limits);
    public boolean getprv(){
        if(counter == 0)
            return true;
            return false;
    public boolean getnxt(){
        if((counter + limits) > recNum)
            return true;
            return false;

    please help me out i am open to any idea you suggest.

  • There is a good article about pagination in Apex here: that lists various options.

    You have two options for pagination inside of Visualforce pages, OFFSET pagination, and StandardSetController pagination. With OFFSET pagination you are limited to 2,000 records; however, with a StandardSetController you can paginate up to 10,000. So, one thing you could do would be to refactor to use the StandardSetController pagination.

    Another thing that you could do, would be to add some sort of filtering capability, so that the user can get to the records they need. For example, if you would return more than 2,000 records, display a message saying something like "Showing 2,000 records but there could be more. Please use a filter to narrow down the results."

    what if it i need to do to display more than 10000 records.

    Filtering is a good idea. What human could grok 10,000 or more records? And if a computer is consuming the data, why use VisualForce? Sounds like this is one platform limitation that really does protect developers from awful requirements, and gives us a good cop out.

    I don't want all the 10 000 records on the VF page at once. If the user have 30 000 records in his object. Then what will be the solution for that. If we display more 50 records per page that is total 200 page or 100 records per page that is 100 page this if the user need to move to 101 page than what ? sorry if i am wrong. just want to clear why 10 000 limit on pagination. Is there any method to improve 10 000. I know it is silly but crazy to know more do anybody have any new idea. That's it.

    The 10,00 limit is imposed by the platform. I suppose you could do something where you mimic offset yourself programmatically, but the actual StandardSetController is limited to 10K.

    Is it not possible to update the list on the Vf page used for pagination. When i changed the current value and hit next button i am getting an error that Modified rows exist in the records collection!.

License under CC-BY-SA with attribution

Content dated before 7/24/2021 11:53 AM