I have a query like :
select * from mytable order by mycol_1 OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
Now, if in this iteration ,I delete several records out of the 10 records fetched by above query and then run :
select * from mytable order by mycol_1 OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;
Will I be getting the real next 10 records or does the SQL engine shift up some rows and thus skip some of the rows ?
Advertisement
Answer
For example: if you have 20 rows (let’s suppose that those are ordinal numbers from 1 to 20) and delete e.g. 2, 4, 6, 8, 10 and then run your 2nd statement (offset 10): it will “skip” values 1, 3, 5, 7, 9, 11, 12, 13, 14, 15 (which make offset = 10) and return values from 16 onwards.
If you again use offset 0, you’d get 1,3, 5, 7, 9, 11, 12, 13, 14, 15 as result which means that nothing will be skipped (except, of course, rows that are deleted, they don’t exist any more).
Basically, it means that you can omit OFFSET entirely and work with FETCH FIRST 10 ROWS ONLY option.
By the way, you’re right in using the order by clause; I’ve seen people “hoping” that query will automagically return rows they *think” SQL will return, but – without the order by clause, that’s unpredictable.