Re: Finding out last 10 records
Rownum is not necessarily useless for this kind of query. In versions of Oracle (8i+) where an inline view can include an order by, it can be rather useful. Top 5 salaries: select ename, sal from ( select ename, sal from scott.emp order by sal desc ) a where rownum 6 Jared On Wednesday 14 August 2002 08:43, Jamadagni, Rajendra wrote: This email just tells us that such questions need to be included in FAQ ... Bottom line is * No matter how you use ROWNUM, it is USELESS. * No matter how you use ROWID, it is USELESS. * If you don't have a PK or any type of unique incrementing key or a timestamp with a key it won't help you. * What the He$$ is 'Last Rows' and 'First Rows'? * First rows as in * first 10 rows by employee salary? * first 10 rows by employee lastname? * first 10 rows by employee first? * first 10 rows by employee birth date? * first 10 rows by employee date of joining by work? You see, unless you define I mean clearly define what do you mean by last and first, NOTHING will help you. We as humans distinctively turn to chronological order when someone tells last or first. But is that what the original poster wants? The original poster himself is not clear on the requirements so any number of solution will not help. Santosh, this has been repeated many times, but here it is once again ... 'SQL by itself will NOT return rows in any particular order, because default order is implementation dependent.' In other words Oracle can return that data in any damn order it pleases. That's precisely why the wise men who developed SQL standards gave us 'ORDER BY' clause. Now, ORDER BY clause must be used with (one or more) parameter(s) i.e. a column or expression. This column or expression will help you determine your first and last requirements. Timestamp alone is not sufficient in case of chronological order. What if my system inserts 25 rows in less than one second, how would you determine the order then? There now I feel better ... I think this should be one of those 10 commandments for developers .. I am still surprised by number of replies that include rownum and rowid without ORDER BY ... Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! Content-Type: text/plain; charset=iso-8859-1; name=ESPN_Disclaimer.txt Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
RE: Finding out last 10 records
Jared, Probably it is not clear from my post, but ROWNUM is still useless WITHOUT an order by. And original poster had no clue on what to order by to start with. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, August 15, 2002 2:43 AM To: Multiple recipients of list ORACLE-L Rownum is not necessarily useless for this kind of query. In versions of Oracle (8i+) where an inline view can include an order by, it can be rather useful. Top 5 salaries: select ename, sal from ( select ename, sal from scott.emp order by sal desc ) a where rownum 6 Jared *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Finding out last 10 records
Take the emp table as an example Select rownum, empno from emp where rownum ( Select count(*) + 1 from emp) minus Select rownum, empno from emp where rownum ( Select (count(*) + 1) - 10 from emp) Hope this works Fazal --- Amjad Saiyed [EMAIL PROTECTED] wrote: hi milind iam not sure but does = will work coz with rownum i thought only worked.hence we could use 11 i not sure can u let me know ... thanx and rgds, Ams -Original Message- Sent: Wednesday, August 14, 2002 7:13 PM 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 worlds 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: 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). = Abul Fazal Production Support Services - Quantum Leap Standard Charted Bank Singapore HP : 65-94887900 __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abul Fazal 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).
RE: Finding out last 10 records
You are right, I didn't get that from your post, though I thought it unlikely you didn't know it. :) Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/15/2002 08:53 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Finding out last 10 records Jared, Probably it is not clear from my post, but ROWNUM is still useless WITHOUT an order by. And original poster had no clue on what to order by to start with. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, August 15, 2002 2:43 AM To: Multiple recipients of list ORACLE-L Rownum is not necessarily useless for this kind of query. In versions of Oracle (8i+) where an inline view can include an order by, it can be rather useful. Top 5 salaries: select ename, sal from ( select ename, sal from scott.emp order by sal desc ) a where rownum 6 Jared *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Finding out last 10 records
Hi Santosh, i assume that u will b orderingthe query based on some criteria so say u have tableA with column x for finding the 1st 10records we will use the following query: select x from A where rownum 11order by 1 / for finding thelast 10records we will use the following query: select x from A where rownum 11order by 1 desc / the only difference is in the ordering... i hope this helps u out.. rgds, Ams.. www.medicomsoft.com -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Santosh VarmaSent: Wednesday, August 14, 2002 1:49 PMTo: Multiple recipients of list ORACLE-LSubject: 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
Re: Finding out last 10 records
AFAIK Oracle keeps no timestamp of when a record was inserted.It also reuses the space freed up by deletes which means rownum can not be used either So I don't think that it's possible to do such thing and have any guaranteeswithout extra sql . The only way to go is to add a kolom and fill that with a sequence number (overhead). Timestamp may work but only limited to whole second I believe. Jack Santosh Varma [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] t.net cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Finding out last 10 records [EMAIL PROTECTED] 14-08-2002 11:48 Please respond to ORACLE-L 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 === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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
Re: Finding out last 10 records
Check the doc: SQL reference, basic elements of Oracle SQL pseudocolumns. The explanation on rownum gives you examples for your problem. Yechiel AdarMehish - 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
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 usinginformation 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
RE: Finding out last 10 records
i thought santosh wanted records based on value that is 1st 10 minimalvalues 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 GorbatchevSent: Wednesday, August 14, 2002 3:08 PMTo: Multiple recipients of list ORACLE-LSubject: 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 usinginformation 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
Re: Finding out last 10 records
If you have a sequence column in table ála primary key: select * from (select * from table order by sequence desc) where rownum 11; Bu you don't have it - no way how to determine last 10 inserted records in table. Jan Pruner - 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: 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).
RE: Finding out last 10 records
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 3from emp 4order 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
RE: Finding out last 10 records
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 worlds 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).
Re: Finding out last 10 records
Jack, From 9.0.1 TIMESTAMP (precision) datatype can store fractions of second specified by precision. Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 14, 2002 12:28 PM AFAIK Oracle keeps no timestamp of when a record was inserted.It also reuses the space freed up by deletes which means rownum can not be used either So I don't think that it's possible to do such thing and have any guaranteeswithout extra sql . The only way to go is to add a kolom and fill that with a sequence number (overhead). Timestamp may work but only limited to whole second I believe. Jack Santosh Varma [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] t.net cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Finding out last 10 records [EMAIL PROTECTED] 14-08-2002 11:48 Please respond to ORACLE-L 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 === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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: Alexandre Gorbatchev 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).
RE: Finding out last 10 records
This email just tells us that such questions need to be included in FAQ ... Bottom line is * No matter how you use ROWNUM, it is USELESS. * No matter how you use ROWID, it is USELESS. * If you don't have a PK or any type of unique incrementing key or a timestamp with a key it won't help you. * What the He$$ is 'Last Rows' and 'First Rows'? * First rows as in * first 10 rows by employee salary? * first 10 rows by employee lastname? * first 10 rows by employee first? * first 10 rows by employee birth date? * first 10 rows by employee date of joining by work? You see, unless you define I mean clearly define what do you mean by last and first, NOTHING will help you. We as humans distinctively turn to chronological order when someone tells last or first. But is that what the original poster wants? The original poster himself is not clear on the requirements so any number of solution will not help. Santosh, this has been repeated many times, but here it is once again ... 'SQL by itself will NOT return rows in any particular order, because default order is implementation dependent.' In other words Oracle can return that data in any damn order it pleases. That's precisely why the wise men who developed SQL standards gave us 'ORDER BY' clause. Now, ORDER BY clause must be used with (one or more) parameter(s) i.e. a column or expression. This column or expression will help you determine your first and last requirements. Timestamp alone is not sufficient in case of chronological order. What if my system inserts 25 rows in less than one second, how would you determine the order then? There now I feel better ... I think this should be one of those 10 commandments for developers .. I am still surprised by number of replies that include rownum and rowid without ORDER BY ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Finding out last 10 records
No, it will not work. You cannot use ROWID to sort records to get 'insert-order'. It is wrong. And you have to use dynamic view if you want ORDER BY and use ROWNUM. Jan Pruner -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 -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- 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).
RE: Finding out last 10 records
Whoops! * No matter how you use ROWID, it is USELESS. Really? Even for deleting duplicate rows? (well, lets overlook fact they shouldn't have been there anyway...) peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
RE: Finding out last 10 records
(snipped a lot from Dan's reply..) 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. ... to point out that 'group by' will allow you to achieve sorted order and application of rownum. peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
RE: Finding out last 10 records
reference to context ... reference to context ... Thanks Peter Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, August 14, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Whoops! * No matter how you use ROWID, it is USELESS. Really? Even for deleting duplicate rows? *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Finding out last 10 records
Spot on! Using DISTINCT and indexes will also cause implicit sorting. -Original Message- Sent: Wednesday, August 14, 2002 11:04 AM To: Multiple recipients of list ORACLE-L (snipped a lot from Dan's reply..) 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. ... to point out that 'group by' will allow you to achieve sorted order and application of rownum. peter edinburgh * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments.http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
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 3from emp 4order 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
RE: Finding out last 10 records
hi milind iam not sure but does = will work coz with rownum i thought only worked.hence we could use 11 i not sure can u let me know ... thanx and rgds, Ams -Original Message- Sent: Wednesday, August 14, 2002 7:13 PM 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 worlds 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: 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).
