title: Select range (limit) of results in Oracle SQL / pagination
body: <p>Let's say we have the following table:</p>
  
  <pre><code>CREATE TABLE "ARTICLE_COUNTER_STATISTICS_M"
  (
    "ID"        NUMBER(19,0) NOT NULL ENABLE,
    "ITEMCOUNT" NUMBER(19,0),
    "VERSION" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
  )
  </code></pre>
  
  <p>Unique constraint is on ID and VERSION field.</p>
  
  <p>Example of some rows that could be in DB(Timestamps is be the same for all records always):</p>
  
  <pre><code>1374659422641   22  2014.02.26 09:45:01,000000000
  1387797258001   7   2014.02.26 09:45:01,000000000
  1387796687862   1   2014.02.26 09:45:01,000000000
  1387800521317   1   2014.02.26 09:45:01,000000000
  </code></pre>
  
  <p>Now, if we want to select the IDs, itemcount and order them by itemcount we would be doing something like this:</p>
  
  <pre><code>SELECT id, SUM(itemcount) as count, version 
  FROM ARTICLE_COUNTER_STATISTICS_m 
  WHERE id != '0' 
  GROUP BY id, version 
  ORDER BY version DESC, SUM(itemcount) DESC 
  </code></pre>
  
  <p>But the thing that is unclear, how do we select results only in certain range. For example 10 to 20 items with most count? I tried something like this:</p>
  
  <pre><code>SELECT id, count, version FROM( 
      SELECT id, SUM(itemcount) as count, version 
      FROM ARTICLE_COUNTER_STATISTICS_m 
      WHERE id != '0' 
      GROUP BY id, version 
      ORDER BY version DESC, SUM(itemcount) DESC 
  ) where rownum &gt;= 0 and rownum &lt;= 20
  </code></pre>
  
  <p>But it does not work with if n in "where rownum >= n" is greater than 1 (it just returns empty results). I know that probably ROW_NUMBER() function has to be used to accomplish the goal, but I could not get it working. Any ideas? Thx!</p>