title: How does SELECT TOP with ORDER BY into DERIVED TABLE work in Sybase ASE?
body: <p>I was wondering how this query is executed:</p>
  
  <p>I got the error message "An ORDER BYclause is not allowed in a derived table." same using <strong>TOP</strong>.</p>
  
  <p>NOT WORK:</p>
  
  <pre><code>SELECT
      count(*),
      sum(a_metric_1),
      a_fld_1
  FROM
      (
          SELECT
              sales AS a_metric_1,
              product AS a_fld_1
          FROM
              bi_sales
      ) sel1
  INNER JOIN (
      SELECT
          TOP 10 product AS b_fld_1
      FROM
          bi_sales
      GROUP BY
          product
      ORDER BY
          sum(sales) DESC
  ) sel2 ON sel1.a_fld_1 = sel2.b_fld_1
  GROUP BY
      a_fld_1
  ORDER BY
      a_fld_1
  </code></pre>
  
  <p>SELECT's work individually, but do not work in a <strong>derived table</strong>.</p>
  
  <p>WORK:</p>
  
  <pre><code>SELECT
      TOP 10 product AS b_fld_1
  FROM
      bi_sales
  GROUP BY
      product
  ORDER BY
      sum(sales) DESC
  </code></pre>
  
  <p>I need to run SQL using derived table with ORDER BY and TOP functions. Because I work with development software to generate PHP code, it's a RAD. So the user should inform your own SQL, can be simple or complex.</p>
  
  <p>Imagine that "sel1" which may be another SQL complex with JOIN's, CASES ...</p>
  
  <p>Thus, we treat the user SQL as the "sel1". We reply using the TOP ORDER BY with the "sel2" and then make a new external SELECT with comparative JOIN in "sel1" and "sel2".</p>
  
  <p>On Sybase don't work:</p>
  
  <pre><code>SELECT column FROM ( SELECT top 10 column2 FROM table ORDER BY column2 )
  </code></pre>
  
  <p>The same statement work on MS SQL Server, MySQL, Oracle, MS Access, PostgreSQL, SQLite, Firebird and Informix.</p>
  
  <p>Thanks in advance for your replies !</p>