title: SQL to Paginate Data Where Pagination Starts at a Given Primary Key
body: <p><strong>Edit:</strong> The original example I used had an int for the primary key when in fact my primary key is a var char containing a UUID as a string. I've updated the question below to reflect this.</p>
  
  <p><strong>Caveat</strong>: Solution must work on postgres.</p>
  
  <p><strong>Issue</strong>: I can easily paginate data when starting from a known page number or index into the list of results to paginate but how can this be done if all I know is the primary key of the row to start from. Example say my table has this data</p>
  
  <pre><code>TABLE: article
  ======================================
  id           categories    content
  --------------------------------------
  B7F79F47     local         a
  6cb80450     local         b
  563313df     local         c
  9205AE5A     local         d
  E88F7520     national      e
  5ab669a5     local         f
  fb047cf6     local         g
  591c6b50     national      h
  ======================================
  </code></pre>
  
  <p>Given an article primary key of '9205AE5A' (article.id == '9205AE5A') and categories column must contain 'local' what sql can I use to return a result set that includes the articles either side of this one if it was paginated i.e. the returned result should contain 3 items (previous, current, next articles)</p>
  
  <pre><code>('563313df','local','c'),('9205AE5A','local','d'),('5ab669a5','local','f')
  </code></pre>
  
  <p>Here is my example setup:</p>
  
  <pre><code>-- setup test table and some dummy data
  create table article (
    id varchar(36),
    categories varchar(256),
    content varchar(256) 
  )
  insert into article values
  ('B7F79F47', 'local', 'a'),
  ('6cb80450', 'local', 'b'),
  ('563313df', 'local', 'c'),
  ('9205AE5A', 'local', 'd'),
  ('E88F7520', 'national', 'e'),
  ('5ab669a5', 'local', 'f'),
  ('fb047cf6', 'local', 'g'),
  ('591c6b50', 'national', 'h');
  </code></pre>
  
  <p>I want to paginate the rows in the article table but the starting point I have is the 'id' of an article. In order to provide a "Previous Article" and "Next Article" links on the rendered page I also need the  articles that would come either side of this article I know the id of </p>
  
  <p>On the server side I could run my pagination sql and iterate through each result set to find the index of the given item. See the following inefficient pseudo code / sql to do this: </p>
  
  <pre><code>page = 0;
  resultsPerPage = 10;
  articleIndex = 0;
  do {
      resultSet = select * from article where categories like '%local%' limit resultsPerPage offset (page * resultsPerPage) order by content;
      for (result in resultSet) {
          if (result.id == '9205AE5A') {
             // we have found the articles index ('articleIndex') in the paginated list. 
             // Now we can do a normal pagination to return the list of 3 items starting at the article prior to the one found
             return select * from article where categories like '%local%' limit 3 offset (articleIndex - 1);
          }
          articleIndex++;
      }
      page++;
  } while (resultSet.length &gt; 0);
  </code></pre>
  
  <p>This is horrendously slow if the given article is way down the paginated list. How can this be done without the ugly while+for loops?</p>
  
  <p><strong>Edit 2</strong>: I can get the result using two sql calls </p>
  
  <pre><code>SELECT 'CurrentArticle' AS type,* FROM
    (
     SELECT (ROW_NUMBER() OVER (ORDER BY content ASC)) AS RowNum,* 
     FROM article 
     WHERE categories LIKE '%local%' 
     ORDER BY content ASC
    ) AS tagCloudArticles
  WHERE id='9205AE5A' 
  ORDER BY content ASC 
  LIMIT 1 OFFSET 0
  </code></pre>
  
  <p>From that result returned e.g.</p>
  
  <pre><code>('CurrentArticle', 4, '9205AE5A', 'local', 'd')
  </code></pre>
  
  <p>I can get the RowNum value (4) and then run the sql again to get RowNum+1 (5) and RowNum-1 (3)</p>
  
  <pre><code>SELECT 'PrevNextArticle' AS type,* FROM
    (
     SELECT (ROW_NUMBER() OVER (ORDER BY content ASC)) AS RowNum,* 
     FROM article 
     WHERE categories LIKE '%local%' 
     ORDER BY content ASC
    ) AS tagCloudArticles
  WHERE RowNum in (3, 5)
  ORDER BY content ASC 
  LIMIT 2 OFFSET 0
  </code></pre>
  
  <p>with result</p>
  
  <pre><code>   ('PrevNextArticle', 3, '563313df', 'local', 'c'),
     ('PrevNextArticle', 5, '5ab669a5', 'local', 'f')
  </code></pre>
  
  <p>It would be nice to do this in one efficient sql call though.</p>