title: Query next/previous record
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/203302/what-is-the-sql-for-next-and-previous-in-a-table">What is the SQL for &#39;next&#39; and &#39;previous&#39; in a table?</a>  </p>
  </blockquote>
  
  
  
  <p>I'm trying to find a better way to get the next or previous record from a table.  Let's say I have a blog or news table:</p>
  
  <pre><code>CREATE TABLE news (
      news_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
      news_datestamp DATETIME NOT NULL,
      news_author VARCHAR(100) NOT NULL,
      news_title VARCHAR(100) NOT NULL,
      news_text MEDIUMTEXT NOT NULL
  );
  </code></pre>
  
  <p>Now on the frontend I want navigation buttons for the next or previous records, if i'm sorting by <code>news_id</code>, I can do something rather simple like:</p>
  
  <pre><code>SELECT MIN(news_id) AS next_news_id FROM news WHERE news_id &gt; '$old_news_id' LIMIT 1
  SELECT MAX(news_id) AS prev_news_id FROM news WHERE news_id &lt; '$old_news_id' LIMIT 1
  </code></pre>
  
  <p>But the news can be sorted by any field, and I don't necessarily know which field is sorted on, so this won't work if the user sorts on <code>news_author</code> for example.</p>
  
  <p>I've resorted to the rather ugly and inefficient method of sorting the entire table and looping through all records until I find the record I need.</p>
  
  <pre><code>$res = mysql_query("SELECT news_id FROM news ORDER BY `$sort_column` $sort_way");
  $found = $prev = $next = 0;
  while(list($id) = mysql_fetch_row($res)) {
      if($found) {
          $next = $id;
          break;
      }
      if($id == $old_news_id) {
          $found = true;
          continue;
      }
      $prev = $id;
  }
  </code></pre>
  
  <p>There's got to be a better way.</p>
  
  <p>Edit, clarifications:</p>
  
  <p>Why dont I use limit? I would need to know the position in the result set of the current record, which I don't. If this were your typical pagination, and I had query strings like <code>?startpage=n</code> then yes that would work, I could just increment <code>$startpage</code> and add <code>LIMIT $startpage,1</code> to the query.  But I have urls like <code>news/news_id-news-title-here</code> that are rewritten to <code>?news_id=n</code>, so I don't know what the startpage is.  Even if I did, what if the user gets there via an external link?  What if new posts are added while the user is reading the current page? </p>
  
  <p>Don't get too stuck on the specifics of the example above, the real question is this: </p>
  
  <p><strong>Given a unique record id and an arbitrary sort column, is there a way to determine which records fall immediately before and after that specific record, without looping through the entire record set.</strong></p>