I am writing a very simple query for an Oracle DB (version 9).
Somehow I can get first 5 rows:
select * from cities where rownum <= 5
But skipping 5 rows returns an empty result:
select * from cities where rownum >= 5
Using:
Why is the second query returning an empty result?
In Oracle Database 12c (release 1) and above, you can do this very simple, for skip 5 rows:
SELECT * FROM T OFFSET 5 ROWS and for skip 5 rows and take 15 rows:
SELECT * FROM T OFFSET 5 ROWS FETCH NEXT 15 ROWS ONLY You can use the following query to skip the first not n of rows.
select * from ( select rslts.*, rownum as rec_no from ( <<Query with proper order by (If you don't have proper order by you will see weird results)>> ) rslts ) where rec_no > <<startRowNum - n>> The above query is similar to pagination query below.
select * from ( select rslts.*, rownum as rec_no from ( <<Query with proper order by (If you don't have proper order by you will see weird results)>> ) rslts where rownum <= <<endRowNum>> ) where rec_no > <<startRowNum>> Your cities query:
select * from ( select rslts.*, rownum as rec_no from ( select * from cities order by 1 ) rslts ) where rec_no > 5 <<startRowNum>> Note: Assume first column in cities table is unique key
Oracle increments rownum each time it adds a row to the result set. So saying rownum < 5 is fine; as it adds each of the first 5 rows it increments rownum, but then once ruwnum = 5 the WHERE clause stops matching, no more rows are added to the result, and though you don't notice this rownum stops incrementing.
But if you say WHERE rownum > 5 then right off the bat, the WHERE clause doesn't match; and since, say, the first row isn't added to the result set, rownum isn't incremented... so rownum can never reach a value greater than 5 and the WHERE clause can never match.
To get the result you want, you can use row_number() over() in a subquery, like
select * from (select row_number() over() rn, -- other values from table where -- ...) where rn > 5 Update - As noted by others, this kind of query only makes sense if you can control the order of the row numbering, so you should really use row_number() over(order bysomething) where something is a useful ordering key in deciding which records are "the first 5 records".
over(order by MyColumn)
select * from (select row_number() over (order by id) rn, c.* from cities c ) where rn > 3 ? Error: %s: invalid identifier
rn in row_number() over(order by id) rnrownum is being increased only when a row is being output, so this type of condition won't work.
In any case, you are not ordering your rows, so what's the point?
Used row_number() over (order by id):
select * from (select row_number() over (order by id) rn, c.* from countries c) where rn > 5 Used ROWNUM:
select * from (select rownum rn, c.* from countries c) where rn > 5 Important note:
Using alias as countries c instead of countries is required! Without, it gives an error "missing expression"
row_number() over(...) expression to rn so rn is undefined. Because row_number() over() is standard whereas rownum is Oracle-specific, I recommend using the former.ID column? That's the identifier it's complaining about...
Even better would be:
select * from mytab sample(5) fetch next 1 rows only; Sample clause indicates the probability of each row getting picked up in the sampling process. FETCH NEXT clause indicates the number of rows you want to select.
With this code, you can query your table with skip and take.
select * from ( select a.*, rownum rnum from ( select * from cities ) a ) WHERE rnum >= :skip + 1 AND rnum <= :skip + :take This code works with Oracle 11g. With Oracle 12, there is already a better way to perform this queries with offset and fetch
FETCH FIRST, but I don't know from which version... Try addingFETCH FIRST 5 ROWS ONLYat the end. Combine withORDER BY!