Select a range (limit) of results in Oracle SQL / pagination

Question

Say we have the following table:

CREATE TABLE "ARTICLE_COUNTER_STATISTICS_M"
(
  "ID"        NUMBER(19,0) NOT NULL ENABLE,
  "ITEMCOUNT" NUMBER(19,0),
  "VERSION" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
)

A unique restriction is in the ID and VERSION fields.

An example of some rows that may be in the database (Timestamps are always the same for all records):

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

Now, if we want to select identifiers, elements and arrange them by element, we will do something like this:

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 

But it is unclear how we select results only in a certain range. For example, from 10 to 20 elements with the most counters? I tried something like this:

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 >= 0 and rownum <= 20

But this will not work if n in "where rownum> = n" is greater than 1 (it just returns empty results). I know that to achieve the goal, you need to use the ROW_NUMBER () function, but I could not get it to work. Any ideas? thanks!

Answer 1

row_number:

SELECT * 
FROM   (SELECT id, itemcount, version, 
               ROW_NUMBER() OVER (ORDER BY version DESC, itemcount DESC) AS rn
        FROM   (SELECT id, SUM(itemcount) as itemcount, version 
                FROM ARTICLE_COUNTER_STATISTICS_m 
                WHERE id != '0' 
                GROUP BY id, version
               )
        )
WHERE  rn BETWEEN 3 AND 20 -- just an example

, row_number ( ). , , , version=100 itemcount=100, rn BETWEEN 2 AND 4, , . , rank, ( , ).

Answer 2

,

SELECT * 
FROM(
    SELECT a.*, rownum rn
    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
          ) a
    WHERE ROWNUM <= 20
    )
WHERE rn > 0;