10

Why does the following query return 'zero' records:

SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10

     OR

SELECT * FROM records WHERE rownum >= 5

Whereas the following query return proper records:

SELECT * FROM records WHERE rownum <= 15

Regards,
- Ashish

Vini
  • 8,299
  • 11
  • 37
  • 49
  • You can always use a minus to subtract the results of a <= rownum query from the entire query. –  Jul 03 '12 at 19:30

3 Answers3

32

In Oracle, Rownum values are assigned after the filtering stage of the query - they are not rows of the table, they are rows of the query result set.

So the first row that comes back will always be given rownum 1, the second row that comes back rownum 2, etc.

The rownum value is only incremented after it is assigned, so any query like

select * from t where ROWNUM > 1

will never return any results. This query says 'I dont want to see the first row that gets returned to me, only the ones after that' which is sortof a paradox so nothing gets returned.

See Ask Tom:On ROWNUM and Limiting Results for more details.

codeulike
  • 22,514
  • 29
  • 120
  • 167
  • Is it possible to access the rownum of a subquery within a larger query? I suspect that might be what the original question is after. – Greg Hewgill May 12 '09 at 23:46
  • Ah, I see Tom explains that in the article you linked, in the "Pagination with ROWNUM" section. Great link! – Greg Hewgill May 12 '09 at 23:54
  • Hmm !! and I thought I could use 'rownum' to filter out any subset of records. That was a real detailed and informative answer - thanks 'codeulike' for your help. – Vini May 13 '09 at 00:06
  • Embarassingly, all I did was google it and write a summary - I work with MS-SQL, not Oracle. But this is now my highest voted answer : ) thanks! – codeulike May 13 '09 at 00:18
  • Link in answer broken. New link here: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html – CodeClimber Jul 18 '12 at 15:16
1

ROWNUM is a pseudocolumn and it's value will not be available at the time of query execution. So you can't filter your results based on this column. With that said, there are work arounds to mitigate it. Please see below (it is not recommended to use if your result is very huge).

Select a.* From 
(
  Select COL1, COL2, ROWNUM RowNumber From MyTable
) a
Where
   RowNumber = 5;
ngrashia
  • 9,869
  • 5
  • 43
  • 58
Shivraaz
  • 207
  • 2
  • 3
1

Alternative is to use MINUS

SELECT * FROM records 
WHERE   ROWNUM <= 10
minus SELECT * FROM records 
WHERE   ROWNUM <= 5

this will filter out non-unique values so you better be selecting id.

Hope this saves you some time.

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265