4

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!

Improve this question
1
  • 1
    Not related to your question, but using "WHERE id != '0' " is going to hurt your performance badly - Oracle will convert the ID column to a string to do the comparison. Use "WHERE id != 0 " instead (without the quotes). Commented Feb 26, 2014 at 8:02

2 Answers 2

Reset to default
2

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you, this also works (count on line 2 has to be changed to itemcount though, I couldn't edit your post). But if I replace ROW_NUMBER() with RANK(), it gives me incorrect results on 2 to 5, for example. Is there any thing else I have to modify for it to work with rank?
Re: count vs. itemcount - fixed, thanks for noticing. Re: wrong results with rank - as I mentioned, items with the same properties will have the same value, so you may get a bit more/less items than you bargained for (but at least it'll be deterministic). Can you give a specific example where it did not act as planned?
Nevermind, it was working correct. It's just that when using rownum example provided by Dba indexing starts from 0, but with ROW_NUMBER() or RANK() starts from 1. Thank you both, Mureinik and Dba for the great answers! :) I would like to mark both as accepted answers, but I have to choose one :(. I will choose Mureinik's, since I gave vote up for Dba already. :)
1

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;
Improve this answer

2 Comments

Thank you, this works! Any ideas what was wrong with my example and why did it not work? Could ROW_NUMBER() have been used instead?
@Javatar, Yes, You can use ROW_NUMBER() too to get this, but it is quite inefficient in ORACLE. Refer the below link to know the difference b/w ROWNUM and ROW_NUMBER. explainextended.com/2009/05/06/oracle-row_number-vs-rownum

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.