| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Finding out last 10 records
but wont the following work , try it out...
select rownum, empno, ename
from emp
where rownum < 11
order by ename
plz do revert back...
rgds,
Ams..
-----Original Message-----
Sent: Wednesday, August 14, 2002 7:34 PM
To: Multiple recipients of list ORACLE-L
Unfortunately, this solution will not work. The ordering of the columns is done AFTER the assignment of rownum. Rownum is the order of retrieval, not the order of display.
1 select rownum, empno, ename
2* from emp
ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
1 select rownum, empno, ename
2 from emp
3* where rownum <= 10
ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
1 select rownum, empno, ename
2 from emp
3 where rownum <= 10
4* order by ename
ROWNUM EMPNO ENAME
---------- ---------- ----------
2 7499 ALLEN
6 7698 BLAKE
7 7782 CLARK
4 7566 JONES
9 7839 KING
5 7654 MARTIN
8 7788 SCOTT
1 7369 SMITH
10 7844 TURNER
3 7521 WARD
As you can see, the rownum is not altered with the sort order. In order to retrieve the first 10 records by name, you need to have the output sorted prior to assignment of rownum.
1 select rownum, i.rownum_int, i.empno, i.ename 2 from (select rownum rownum_int, empno, ename
3 from emp 4 order by ename) i
ROWNUM ROWNUM_INT EMPNO ENAME
---------- ---------- ---------- ----------
1 11 7876 ADAMS
2 2 7499 ALLEN
3 6 7698 BLAKE
4 7 7782 CLARK
5 13 7902 FORD
6 12 7900 JAMES
7 4 7566 JONES
8 9 7839 KING
9 5 7654 MARTIN
10 14 7934 MILLER
For more information, please see my paper at http://www.optimaldba.com/library/TopNRow.html. It is a little outdated, but the concepts are still valid. A more up to date treatment can be found in the SQL Scripting Sorcery paper/presentation at the same site.
Dan Fink
-----Original Message-----
Sent: Wednesday, August 14, 2002 9:13 AM
To: Multiple recipients of list ORACLE-L
Hi Santosh,
If I understand your requirement correctly, this could be the solution
for first 10 records :
select * from TABLE_NAME where rownum<=10 order by rowid;
for last 10 records :
select * from contract where rownum<=10 order by rowid desc;
MILIND SHITOLE
milinds_at_sage.co.za
>From: "Amjad Saiyed" <amjad_at_medicomsoft.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Finding out last 10 records
>Date: Wed, 14 Aug 2002 04:28:24 -0800
>
>i thought santosh wanted records based on value that is 1st 10 minimal
>values and last 10 max values...well if this is not the case than the
>solution that i had send using rownum is obsolete and hence ignore it....
>
>rgds,
>Ams.
>www.medicomsoft.com.
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Alexandre
>Gorbatchev
> Sent: Wednesday, August 14, 2002 3:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Finding out last 10 records
>
>
> Santosh,
>
> There is no "last" and "first" records in relational databases in terms
>of
>location. All records are equal and may be stored physically anywhere. I
>guess that may be possible (parsing ROWID and using information about
>extent
>location) to sort records by extent, block and number of record inside
>block, but that's not a trivial task.
> If you need to select last 10 inserted records, make a trigger with
>filling a timestamp into a column and select with sort by this column using
>rownum in where condition.
>
> Alexandre
> ----- Original Message -----
> From: Santosh Varma
> To: Multiple recipients of list ORACLE-L
> Sent: Wednesday, August 14, 2002 11:48 AM
> Subject: Finding out last 10 records
>
>
> Hello all
> Could any one of you guide me on how to select last 10 records
>from
>a table. and also finding out first 10 records..
>
> Thanks and regards,
>
> Santosh
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Milind Desh INET: milind_other_at_hotmail.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: Dan.Fink_at_mdx.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amjad Saiyed INET: amjad_at_medicomsoft.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 15 2002 - 00:48:20 CDT
 |
 |