Archive

Posts Tagged ‘Oracle’

SQL – N rows

January 19, 2016 Leave a comment

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
Advertisements
Categories: SQL Tags: , ,
%d bloggers like this: