title: SQL Query With Row_Number, order by and where clause
body: <p>I have the following SQL query:</p>
  
  <pre><code>select
       ID, COLUMN1, COLUMN2
  from
       (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE)
  where
       NO between 0 and 100
  </code></pre>
  
  <p>What I am trying to do is to select the first 100 records of the query </p>
  
  <pre><code>select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC
  </code></pre>
  
  <p>And here are the problems:</p>
  
  <ol>
  <li><p>Apparently, the <code>order by</code> clause is not working. I've noticed that I have to add another <code>order by 2 DESC</code> clause, just after <code>(...) from ATABLE</code>, for my query to work. Is there something I do wrong? Or is it expected behaviour? </p></li>
  <li><p>How can I add a <code>where</code> clause? Let's say I need to select only the first 100 records of the table <code>where COLUMN1 like '%value%'</code>. I've tried adding the where clause after <code>(...) from ATABLE</code> but it produced an error... </p></li>
  </ol>
  
  <p>Help? Thanks.</p>
  
  <p>PS: I'm using Oracle 10g R2.</p>