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 <jan_at_pruner.cz> 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: root_at_fatcity.com [mailto:root_at_fatcity.com]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: jan_at_pruner.cz
> >
> > 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: ListGuru_at_fatcity.com (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: jan_at_pruner.cz
>
> 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: ListGuru_at_fatcity.com (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
mainis_at_mfldclin.edu
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: sundeep_maini_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Aug 30 2001 - 02:04:34 CDT
