Select range (limit) of results in Oracle SQL / pa

问题:

Let's 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
)

Unique constraint is on ID and VERSION field.

Example of some rows that could be in DB(Timestamps is be the same for all records always):

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 the IDs, itemcount and order them by itemcount we would be doing 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 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:

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 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!

回答1:

Here's a row_number example:

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

Note that row_number assigns arbitrary order to items with the same properties (as per the window clause). So, if for example, your first three items all have version=100 and itemcount=100 and you use rn BETWEEN 2 AND 4, there's no telling which one will be excluded. To avoid this, you can use rank, which assigns the same value to items of the same properties (again, as defined by the window clause).

回答2:

Try like this,

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;