title: Calculating SQL Server ROW_NUMBER() OVER() for a derived table
body: <p>In some other databases (e.g. DB2, or Oracle with <code>ROWNUM</code>), I can omit the <code>ORDER BY</code> clause in a ranking function's <code>OVER()</code> clause. For instance:</p>
  
  <pre><code>ROW_NUMBER() OVER()
  </code></pre>
  
  <p>This is particularly useful when used with ordered derived tables, such as:</p>
  
  <pre><code>SELECT t.*, ROW_NUMBER() OVER()
  FROM (
      SELECT ...
      ORDER BY
  ) t
  </code></pre>
  
  <p>How can this be emulated in SQL Server? I've found people using <a href="https://stackoverflow.com/a/4812038/521799">this</a> <a href="https://stackoverflow.com/a/9737178/521799">trick</a>, but that's wrong, as it will behave non-deterministically with respect to the order from the derived table:</p>
  
  <pre><code>-- This order here ---------------------vvvvvvvv
  SELECT t.*, ROW_NUMBER() OVER(ORDER BY (SELECT 1))
  FROM (
      SELECT TOP 100 PERCENT ...
      -- vvvvv ----redefines this order here
      ORDER BY
  ) t
  </code></pre>
  
  <p>A concrete example (as can be seen on <a href="http://sqlfiddle.com/#!6/d41d8/7365" rel="nofollow noreferrer">SQLFiddle</a>):</p>
  
  <pre><code>SELECT v, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
  FROM (
    SELECT TOP 100 PERCENT 1 UNION ALL
    SELECT TOP 100 PERCENT 2 UNION ALL
    SELECT TOP 100 PERCENT 3 UNION ALL
    SELECT TOP 100 PERCENT 4
    -- This descending order is not maintained in the outer query
    ORDER BY 1 DESC
  ) t(v)
  </code></pre>
  
  <p>Also, I cannot reuse any expression from the derived table to reproduce the <code>ORDER BY</code> clause in my case, as the derived table might not be available as it may be provided by some external logic.</p>
  
  <p>So how can I do it? Can I do it at all?</p>