title: Programmatically generating top n queries for Microsoft SQL Server from a general query
body: <p>I have a program that generates some standard SQL like <code>SELECT * FROM TEST ORDER BY X</code>
  and passes it (as a string) to a function along with an integer to create a limited query (also a string).  For Oracle (which the application was developed against), this function returns <code>SELECT * FROM (</code>sq<code>) WHERE ROWNUM &lt;=</code>n giving:</p>
  
  <pre><code>SELECT * FROM (SELECT * FROM TEST ORDER BY X) WHERE ROWNUM &lt;= 10
  </code></pre>
  
  <p>which works.</p>
  
  <p>Unfortunately, I can't figure out how to write the equivalent function for Microsoft SQL Server (I'm no MS SQL expert).</p>
  
  <p>I tried:</p>
  
  <pre><code>SELECT TOP 10 FROM (SELECT * FROM TEST ORDER BY X)
  </code></pre>
  
  <p>but that leads to:</p>
  
  <pre><code>Error: Incorrect syntax near the keyword 'from'.
  </code></pre>
  
  <p>Then I tried:</p>
  
  <pre><code>SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X)
  </code></pre>
  
  <p>which gained me:</p>
  
  <pre><code>Error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
  </code></pre>
  
  <p>which was frustrating since I am specifying TOP....</p>
  
  <p>I tried a few other shotgun variations including:</p>
  
  <pre><code>SELECT TOP (10) * FROM (SELECT * FROM TEST ORDER BY X)
  SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) SUBQUERY
  SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) AS SUBQUERY
  SELECT * FROM (SELECT * FROM TEST ORDER BY X) WHERE ROW_NUMBER &lt;= 10
  </code></pre>
  
  <p>but of course, none of them work either.</p>
  
  <p>Is there no hope at all?</p>