NTH row query in Oracle not behaving as expected

Question

Given;

CREATE TABLE T1 (ID INTEGER, DESCRIPTION VARCHAR2(20));
INSERT INTO T1 VALUES (1,'ONE');
INSERT INTO T1 VALUES (2,'TWO');
INSERT INTO T1 VALUES (3,'THREE');
INSERT INTO T1 VALUES (4,'FOUR');
INSERT INTO T1 VALUES (5,'FIVE');
COMMIT;

Why does;

SELECT * FROM 
( SELECT ROWNUM, ID, DESCRIPTION
  FROM T1)
WHERE MOD(ROWNUM,1)=0;

Return

ROWNUM                                     ID DESCRIPTION        
------ -------------------------------------- --------------------
     1                                      1 ONE                  
     2                                      2 TWO                  
     3                                      3 THREE                
     4                                      4 FOUR                 
     5                                      5 FIVE

Whereas;

SELECT * FROM 
( SELECT ROWNUM, ID, DESCRIPTION
  FROM T1)
WHERE MOD(ROWNUM,2)=0;

Return zero rows ???

Confused, expected ROWNUM=(2,4) to be returned...

alias rownum in inner select and reference the alias in the where clause should work... — xQbert, Nov 02 '16 at 20:07
SELECT * FROM ( SELECT ROWNUM as rn, ID, DESCRIPTION FROM T1) as t WHERE MOD(t.rn, 2)=0; — M. Rezaeyan, Nov 02 '16 at 20:08
`ROWNUM`s are assigned after the rows have been filtered by the `WHERE` clause, so any filter which eliminates `ROWNUM` 1 will prevent any rows from being returned. Similarly any query which filters out `ROWNUM` 2 will prevent any row but row 1 from being returned, e.g. `WHERE MOD(ROWNUM, 5) <= 3` will only return the first 3 rows regardless of how many more rows exist in the table, and is equivalent to the `WHERE ROWNUM <= 3` predicate. — Sentinel, Nov 02 '16 at 21:49
This seems to be a variation of [Selecting the second row of a table using rownum](http://stackoverflow.com/q/9240192/266304). Same underlying explanation, anyway. — Alex Poole, Nov 03 '16 at 00:24

xQbert · Answer 1 · 2016-11-03T13:30:45.167

2

SELECT B.* FROM 
( SELECT ROWNUM a, ID, DESCRIPTION
  FROM T1) B
WHERE MOD(A,2)=0;

Reason: Your approach involves running rownum twice. You don't need to; nor really do you want to. Based on order of operations, the where clause will execute before the the outer select; which means the select hasn't determined the values for each row, and the number of rows is not known yet.

Additional:

I would recommend adding an order by to the inline view so the rownumbers are in a expected specific order as opposed to what the engine derives.

edited Nov 03 '16 at 13:30

answered Nov 02 '16 at 20:08

xQbert

34,733
2
41
62

?? What does that even mean? Row numbers are assigned as the rows are created. The first row brought in is assigned rownum = 1, its rownum is not NULL. It just fails the WHERE clause. Then rownum = 1 is assigned to the next row brought in, it fails WHERE again, and so on till no row is actually selected. With your explanation, WHERE ROWNUM <= 10 should fails also. You can try it for yourself to see that's not the case. – Nov 02 '16 at 22:35
@mathguy if that were true then using <>0 would mean I should get all rows right? that's not the case either. So something else is going on. It's like the engine is looking ahead for just 1 row and that is it when evaluating the where clause. `Select rowNum, col1 FROM DUAL CROSS JOIN (Select 'A' col1 from dual UNON SELECT 'B' from dual UNON SELECT 'C' from dual UNON SELECT 'D' from dual) WHERE mod(rownum,2 )<> 0` should return 4 rows, it only returns the 1st. (oddly I couldn't include the correct spelling of union in the comment) – xQbert Nov 03 '16 at 13:33
Using `<>` where? In `mod(rownum, 2)`? No - in that case the first row would get through, then the second would fail, then the next row would become "second" and fail, then the next one, etc. So if you had `mod(rownum, 2) <>` 0 you would get exactly one row, the first one that is retrieved by the query. – Nov 03 '16 at 13:40
1

Try it yourself, and make it more interesting - try `mod(rownum, 5) <> 0`. You will get the first four rows, then every other row will become fifth and fail (because rownum=5 is REASSIGNED each time). You will get a total of four rows, or fewer if there would be fewer in the first place. – Nov 03 '16 at 13:42
Intriguing. That was not my understanding of how it operated obviously. – xQbert Nov 03 '16 at 13:44
No worries. This is explained in the documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm - see towards the end, the last few paragraphs. – Nov 03 '16 at 14:35

David דודו Markovitz · Answer 2 · 2016-11-02T20:38:04.510

You have 2 operations of ROWNUM.
The 1st ROWNUM generates the numbers 1 through 5.
The 2nd ROWNUM doesn't generate anything because for the row the ROWNUM value is 1, but since MOD(1,2)=0 is false, the record is not being outputted and the ROWNUM is not being incremented, failing the condition again and again.

This query, using alias, returns exactly what you have expected:

SELECT * FROM 
( SELECT ROWNUM as rn, ID, DESCRIPTION
  FROM T1)
WHERE MOD(rn,2)=0;

score 0 · Answer 3 · answered Nov 02 '16 at 21:41

Some facts about the ROWNUM pseudo column in Oracle:

The ROWNUM assigned to each row is determined by the order in which Oracle retrieves the row from the DB.
The order in which rows are returned is non deterministic, such that running it once may return rows in one ordering, and a second time around may have a different ordering if the base tables have been reorganized, or Oracle uses a different query plan.
The order in which ROWNUMs are assigned to rows is not necessarily correlated with the that of an order by clause (the order by clause may affect the ROWNUM order since it may cause a different query plan to be used, but the ROWNUMbers are unlikely to match the sort order).
ROWNUMbers are assigned after the records are filtered by the WHERE clause, so if you filter out ROWNUM 1 you will never return any records.
Filtering a subquery that returns an aliased ROWNUM column works because the entire subquery is returned to the outer query before the outer query filters the rows, but the ROWNUMs will still have a non deterministic order.

To successfully return a top N or Nth row query in a deterministic fashion you need to assign row numbers in a deterministic way. One such way is to use the the `ROW_NUMBER' analytic function in a subquery:

select * from
 (select ROW_NUMBER() over (order by ID) rn
       , ID
       , DESCRIPTION
    from T1)
 where rn <= 4 -- Top N

or

 where rn = 4 -- 1st Nth row

or even

 WHERE MOD(rn,2)=0 -- every Nth row

In either case the ORDER BY clause in the ROW_NUMBER analytic function needs to match the granularity of the data otherwise ties in the ordering will again be non deterministic, most likely matching the current ROWNUM ordering.

NTH row query in Oracle not behaving as expected

3 Answers3