Home » SQL & PL/SQL » SQL & PL/SQL » Confusion Regarding Rownum (Oracle 9i,9.2.0.6.0,XP)
Confusion Regarding Rownum [message #338417] Mon, 04 August 2008 23:33 Go to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I don't know but i found it strange when i run below query


SELECT 
 * FROM 
user_constraints 
WHERE ROWNUM=1


It actually gives me the first row.But when i try rownum value other then 1 it actually returns all values null

Not Working

SELECT 
 * 
FROM user_constraints 
WHERE ROWNUM=<any_othervalue>


But when i run it like this

select 
 a.* 
from 
 (
  SELECT 
   rownum srno,user_constraints.* 
  FROM user_constraints)a WHERE a.srno=2


It works fine.

Is this because of that the rownum is generated at the time of displaying the records.But in that case why it works for rownum=1 only??

Regards,
Rajat


[Updated on: Mon, 04 August 2008 23:35]

Report message to a moderator

Re: Confusion Regarding Rownum [message #338419 is a reply to message #338417] Tue, 05 August 2008 00:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about just having a look at documentation before posting?
Database SQL Reference
Chapter 3 Pseudocolumns
Section ROWNUM Pseudocolumn

Regards
Michel
Re: Confusion Regarding Rownum [message #338421 is a reply to message #338419] Tue, 05 August 2008 00:09 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I got my answer.

Thanks Michel.

Regards,
Rajat
Re: Confusion Regarding Rownum [message #338441 is a reply to message #338417] Tue, 05 August 2008 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajatratewal wrote on Tue, 05 August 2008 06:33
But when i try rownum value other then 1 it actually returns all values null

Not true.
It returns no row at all.
Re: Confusion Regarding Rownum [message #338444 is a reply to message #338441] Tue, 05 August 2008 02:19 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes You are right Frank.

Regards,
Rajat

Previous Topic: Cursor Already Open
Next Topic: Compilation Err in Procedure
Goto Forum:
  


Current Time: Mon Dec 05 05:01:05 CST 2016

Total time taken to generate the page: 0.21652 seconds