You can use the Rank () function to rank your rows
based on a simple or complex criteria in a subquery or
an inline view and then extract the requisite set by
using a simple condition on the Rank col. Below is a
simple example just to give you some idea of what is
possible. You may want to take a look at some ref
manual to explore this for meeting your particular
requirement (Best place is Datawarehousing Guide):
SELECT keycol,
some_other_col(s)
FROM (SELECT keycol,
some_other_col(s),
RANK() OVER (ORDER BY keycol) AS
Ranking
FROM mytable
WHERE condition(s)
)
WHERE ranking between 11 and 20; == alter this
cond. to get a different set. You can use more complex
criteria in ranking/ordering your rows.
--- Jan Pruner [EMAIL PROTECTED] wrote:
Does anybody know how to do it better way?
But this will return only the first ten rows not
subsequent sets. How do I
display rows from 11 to 20, 21 to 30 and so on?
ROWNUM is counting output tuples. You cannot use
ROWNUM 10.
I think you have to order select by your primary
key, make filter based on
your primary key and take only first 10 tuples
using ROWNUM.
JP
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Jan Pruner
Sent: Wednesday, August 29, 2001 4:01 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: How to select and display 10 records
at a time from a table
using sql query
select * from ( select column1, column2 from
my_table order by 1) where
ROWNUM 11;
Look at manual how to use ROWNUM.
It's ease to make mistakes with it.
JP
Dne st 29. srpen 2001 10:55 jste napsal(a):
Dear DBA Gurus,
How do I select and display 10 records at a
time from a table using sql
query only?
Thanks and Regards,
Ranganath
DISCLAIMER: This correspondence is confidential
and intended for the
named recipient(s) only. If you are not the
named recipient and receive
this correspondence in error, you must not copy,
distribute or take any
action in reliance on it and you should delete
it from your system and
notify the sender immediately. Unless otherwise
stated, any views or
opinions expressed are solely those of the
author and do not represent
those of Subex Systems Limited.
www.subexgroup.com
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX: (858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed from). You may
also send the HELP command for other information
(like subscribing).
DISCLAIMER: This correspondence is confidential
and intended for the named
recipient(s) only. If you are not the named
recipient and receive this
correspondence in error, you must not copy,
distribute or take any action
in reliance on it and you should delete it from
your system and notify the
sender immediately. Unless otherwise stated, any
views or opinions
expressed are solely those of the author and do
not represent those of
Subex Systems Limited.
www.subexgroup.com
---
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI
[EMAIL PROTECTED]
__
Do You Yahoo!?
Get email alerts NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sundeep maini
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED]