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
5 where rownum <= 10
6* order by i.ename
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
[EMAIL PROTECTED]
>From: "Amjad Saiyed" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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
>
>
_________________________________________________________________
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Milind Desh
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Amjad Saiyed
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).
