By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,278 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,278 IT Pros & Developers. It's quick & easy.

OFFSET function in oracle

P: n/a
Т
I want to retrieve a range of record
such as retrieving 11th to 20th records from 10000 records
In SQL Server, i can use the "OFFSET" function.
so i wanna ask does Oracle provide any function similar to "OFFSET" in SQL
SERVER?

Thanks in advance
Regards
Bun
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
this may help you ....

select * from mytable where rownum <= 20
minus
select * from mytable where rownum <= 10
"Т" <@.@> wrote in message news:<bp**********@imsp212.netvigator.com>...
I want to retrieve a range of record
such as retrieving 11th to 20th records from 10000 records
In SQL Server, i can use the "OFFSET" function.
so i wanna ask does Oracle provide any function similar to "OFFSET" in SQL
SERVER?

Thanks in advance
Regards
Bun

Jul 19 '05 #2

P: n/a
"Christine" <ka******@hotmail.com> wrote...
this may help you ....

select * from mytable where rownum <= 20
minus
select * from mytable where rownum <= 10


Hi Christine,

you're kidding, right?
Jul 19 '05 #3

P: n/a
Well write a little PL/SQL and do it yourself. It might look less
efficient than SQL Server, but that's pretty much what it does
internally.

ETA

"Guido Konsolke" <GK@oblivion.com> wrote in message news:<10***************@news.thyssen.com>...
"Christine" <ka******@hotmail.com> wrote...
this may help you ....

select * from mytable where rownum <= 20
minus
select * from mytable where rownum <= 10


Hi Christine,

you're kidding, right?

Jul 19 '05 #4

P: n/a
VC
select * from
( select t1.*, rownum rn from ( select * from t1 order by x ) t1
where rownum <= upper_limit )
where rn >= lower_limit;
"Т" <@.@> wrote in message news:bp**********@imsp212.netvigator.com...
I want to retrieve a range of record
such as retrieving 11th to 20th records from 10000 records
In SQL Server, i can use the "OFFSET" function.
so i wanna ask does Oracle provide any function similar to "OFFSET" in SQL
SERVER?

Thanks in advance
Regards
Bun

Jul 19 '05 #5

P: n/a
this question seems to pop up every few days

good solution, but don't forget to take a larger look at your application

if the user is paging thru a large set of records, consider some mechanism
to pre-select the PKs for the entire set and then use these to fetch each
page -- the performance trade off can come after only one or two pages. and
depending on your development tool, you could keep one cursor for doing the
PK fetches as needed, and use a second one for grabbing the current page of
data

-- mcs

"VC" <bo*******@hotmail.com> wrote in message
news:Vppvb.202877$275.751945@attbi_s53...
| select * from
| ( select t1.*, rownum rn from ( select * from t1 order by x ) t1
| where rownum <= upper_limit )
| where rn >= lower_limit;
|
|
| "Т" <@.@> wrote in message
news:bp**********@imsp212.netvigator.com...
| > I want to retrieve a range of record
| > such as retrieving 11th to 20th records from 10000 records
| > In SQL Server, i can use the "OFFSET" function.
| > so i wanna ask does Oracle provide any function similar to "OFFSET" in
SQL
| > SERVER?
| >
| >
| >
| > Thanks in advance
| > Regards
| > Bun
| >
| >
|
|
Jul 19 '05 #6

P: n/a
"Guido Konsolke" <GK@oblivion.com> wrote in message news:<10***************@news.thyssen.com>...
"Christine" <ka******@hotmail.com> wrote...
this may help you ....

select * from mytable where rownum <= 20
minus
select * from mytable where rownum <= 10


Hi Christine,

you're kidding, right?


this should work ->

select rownum, * from mytable where rownum <= 20
minus
select rownum, * from mytable where rownum <= 10
Jul 19 '05 #7

P: n/a
how come i can't delete my meg???
THIS should work, really... ->

select rownum, col1, col2 ... from mytable where rownum <= 20
minus
select rownum, col1, col2 ... from mytable where rownum <= 10
* replace (col1, col2 ...) to whatever colname you want to select.
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.