title: Referencing a MySQL sub-select in another query
body: <p>I have a basic MySQL table, <i>terms</i>, comprised of an <i>id</i> and <i>term</i> field.</p>
  
  <p>I want to create an alphabetically sorted dictionary index (in the literal sense), that would list ten 10 terms above the selected term, and 20 below it. An example of this could be found here <a href="http://www.urbandictionary.com/define.php?term=GD2&amp;defid=3561357" rel="nofollow noreferrer">http://www.urbandictionary.com/define.php?term=GD2&amp;defid=3561357</a> where on the left column you see the current term highlighted, and a number of terms above it, and some below, all sorted alphabetically.</p>
  
  <p>As we all know, MySQL doesn't support a ROW_NUMBER() or a similar function so we end up resorting to user variables and sub-selects. I also cannot create a View with user defined variables because MySQL doesn't allow that. Here's what I managed to come up with (and it works):</p>
  
  <pre><code>        SET @row_num := 0;
  
          SELECT
               @term_index := ordered.row_number
          FROM 
          (
              SELECT
                   @row_num := @row_num + 1 AS row_number, terms.*
              FROM
                  terms
              ORDER BY
                  term ASC
          ) AS ordered 
          WHERE
              ordered.term = 'example term';
  
          SET @row_num := 0;
  
          SELECT *
          FROM 
          (
              SELECT
                  @row_num := @row_num + 1 AS row_number, terms.*
              FROM
                  terms
              ORDER BY
                  term ASC
          ) AS ordered
          WHERE
               row_number BETWEEN @term_index - 10 AND @term_index + 20
  </code></pre>
  
  <p>The first SELECT simply finds out the row number of our target term across the entire alphabetically sorted terms table. The second SELECT uses that information to get 10 terms above it and 20 terms below it.</p>
  
  <p>I wonder if there's a way to avoid running the sub-select in the second SELECT query and instead just reference the first one aliased <i>ordered</i>. Is there a more efficient way of accomplishing this without having to resort to manually create a temporary table? What am I doing wrong here?</p>