Hello,
Since no "order by" is specified, there is no guaranty -at all- that
your 79001th row will be the same twice. Keep in mind there is no
specific order in a normal heap table, there is FIFO concept here.
Remember that a table is NOT a sequential file where rows are appended.
I would request a significant field to answer such a question.
Philippe Valentin
Please consider the environment before printing this email.
-----Original Message-----
From: Sumith Jayasuriya
[mailto:oracledba-ezmlmshield-x19250298.[Email address protected]
Sent: Friday, March 28, 2008 5:28 PM
To: LazyDBA Discussion
Subject: RE: Finding the nth row
Select * from (select rownum row_number, a.* from table_name a) qry
Where qry.row_number = 79001
The above query will give you the 79001th row returned in in-line query.
But the row that you get back depends on how Oracle returns the rows.
Sumith
-----Original Message-----
From: Cote Jeff
[mailto:oracledba-ezmlmshield-x81527077.[Email address protected]
Sent: Friday, March 28, 2008 11:17 AM
To: LazyDBA Discussion
Subject: Finding the nth row
Good afternoon, gurus.
I have a table in which deletes and truncates occur regularly. I've now
been tasked with finding the 79,001 row in the table. This table has no
indexes, and is loaded nightly. Doing a query such as
Select * from table_name
Where rownum = 79001;
Returns no rows. There are currently 112,500 rows.
Does anyone have any insight (outside of creating a cursor to run
through the table until it hits the required row) on a simple SQL query
I could use?
Thanks in advance!
Jeff
Jeffrey Cote'
Sr. Oracle Database Admin.
H&R Block, Financial Advisors
313-628-1267
Jeff.[Email address protected] <mailto:Jeff.[Email address protected]
"Aut viam inveniam aut faciam."
Hannibal (247-182 BCE)
Note: This message, including attachments, is from H&R Block Financial
Advisors,
Inc., Member NYSE, SIPC. This is not a solicitation of any order to buy
or
sell. The
information contained herein is deemed to be reliable but is in no way
warranted by us as
to accuracy or completeness. Messages are monitored and retained by the
Company, but
the company cannot guarantee the security of this message. The company,
and
its
associates, may not accept orders to affect purchase or sale
transactions,
accept funds,
securities, or similar instructions via email. If you are not the
intended
recipient of this
message promptly delete this message and notify the sender of the
delivery
error by
return email or calling 1.800.HRBLOCK and selecting the option for
'Financial Services'
You may not forward, print, copy, distribute, or use the information in
this
message if
you are not the intended recipient.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--------------------------------------------------------
This message w/attachments (message) may be privileged, confidential or proprietary, and if you are not an intended recipient, please notify the sender, do not use or share it and delete it. Unless specifically indicated, this message is not an offer to sell or a solicitation of any investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Merrill Lynch. Subject to applicable law, Merrill Lynch may monitor, review and retain e-communications (EC) traveling through its networks/systems. The laws of the country of each sender/recipient may impact the handling of EC, and EC may be archived, supervised and produced in countries other than the country in which you are located. This message cannot be guaranteed to be secure or error-free. This message is subject to terms available at the following link: http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you consent to the foregoing.
--------------------------------------------------------
Oracle LazyDBA home page