Home » SQL & PL/SQL » SQL & PL/SQL » query on "Can one retrieve only the Nth row from a table? " (Oracle 10g)
query on "Can one retrieve only the Nth row from a table? " [message #339031] Wed, 06 August 2008 10:52 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

i would like to clarify my doubt regarding the question "Can one retrieve only the Nth row from a table? "

As known to me, Oracle doesn't guarantee the order of rows while retrieving data from DB. Therefore, are we correct when we try to get the nth row using some query like the one mentioned below?

select * from (select x,rownum rn from t where rownum < 5) where rn =4;

thanks
P
Re: query on "Can one retrieve only the Nth row from a table? " [message #339032 is a reply to message #339031] Wed, 06 August 2008 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>are we correct
It depends upon what exactly you mean by "correct".
Re: query on "Can one retrieve only the Nth row from a table? " [message #339038 is a reply to message #339031] Wed, 06 August 2008 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You always get the 4th row of the result set, but the result set will not return you the rows in the same order and the result set is not the "table", so...

Regards
Michel
Re: query on "Can one retrieve only the Nth row from a table? " [message #339041 is a reply to message #339038] Wed, 06 August 2008 11:15 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
i wanted to know if i will always get '02', if the sample data is as mentioned below.
001
002
01
02
1M
1MM
MM

However, based on your response i understood that the rows can shuffle but i will get output of only 4rth row.

thanks
Re: query on "Can one retrieve only the Nth row from a table? " [message #339045 is a reply to message #339031] Wed, 06 August 2008 11:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079:
Quote:
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

As you do not use ORDER BY clause, you understand it right.
The example seems like simple ASCII string ordering, but who knows whether this rule always applies.
Re: query on "Can one retrieve only the Nth row from a table? " [message #339068 is a reply to message #339031] Wed, 06 August 2008 12:34 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
be2sp1 wrote on Wed, 06 August 2008 17:52
As known to me, Oracle doesn't guarantee the order of rows while retrieving data from DB.

That depends. It holds for the type of query you provided, but as soon as you add an order by, things will start to become more predictable.
If your order by column(s) provide a unique result set, the order of rows will very much be guaranteed.
Previous Topic: DBMS_RANDOM
Next Topic: Sql query needed
Goto Forum:
  


Current Time: Mon Dec 05 10:35:09 CST 2016

Total time taken to generate the page: 0.08642 seconds