On Fri, 21 May 1999 pgrossi@lycosmail.com wrote:
> Hi Ari,
>
> I'm trying to select the last n records stored on a table but I didn't find
> until now a solution to my problem. Could you help me ?
> I tried using rownum way but I don't know if it's the right and/or the best
> way.
>
> Thanks in Advance
>
> Pietro
_______________________________________________________
Comment from Ivan Leong <ivanl@corp.pacfusion.com>:
if there is an ORDER BY clause, rownum in the WHERE clause do _not_
return first|last records at all. this is bec rownum is the row number
of the "unsorted" table before ORDER BY can come into play.
if there is an ORDER BY clause, and to get the Nth to the Mth row,
SELECT sortedFIELD FROM
(
SELECT rownum AS z, sortedField
FROM table1
ORDER BY sortedField
)
WHERE z > N-1
AND z < M-1
for last n records, just change the WHERE clause to
WHERE z > N-1
__________________________________________________________
Pietro,
ROWNUM can only be used to select records from the "front" of the order of
records being returned.
To find the "last" n records depends on what you mean by "last". Oracle
returns records randomly (actually by the way they are stored) unless
there is an ORDER BY CLAUSE.
If this is the order, then you can make a PL/SQL routine. Create a cursor
with any ORDER BY CLAUSES (not mandatory). Then make a counter variable.
When you select a record, increment the counter. After the counter
surpasses a threshold, start returning records (DBMS_OUTPUT.PUT_LINE).
Hope that this helps!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 295+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page