Amit Gupta's Blog

Fetch starting N rows

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num < N+1 with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num < N+1

Some DBs has function like Top and limit

Fetch Nth row

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num = N with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num = N

Fetch Nth to Mth rows

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num between 1003 and 1118 with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num between 1003 and 1118