Home » SQL & PL/SQL » SQL & PL/SQL » rownum gives no data found!!!!!!!!!!!!111
icon4.gif  rownum gives no data found!!!!!!!!!!!!111 [message #341600] Tue, 19 August 2008 08:47 Go to next message
ckb_ORACLE
Messages: 5
Registered: August 2008
Junior Member
Hi,
I write a very simple sql query

select deptno from dept where rownum=3;

and it gives

no rows selected

but if i write

select deptno from dept where rownum<=3;
it generates the result.
DEPTNO
------
10
20
30
Can anybody please tell me why the first quey does not generate the output
30

Thankyou.
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341601 is a reply to message #341600] Tue, 19 August 2008 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


Search this forum for this FAQ.

ROWNOW is "assigned" for actual selected rows.
No rows are returned because 1 is always NOT equal to 3
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341605 is a reply to message #341600] Tue, 19 August 2008 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ and you will find the answer immediately if you open SQL Reference (clue: see TOC).

Regards
Michel
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341612 is a reply to message #341600] Tue, 19 August 2008 09:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ckb_ORACLE chose a title on Tue, 19 August 2008 15:47
rownum gives no data found!!!!!!!!!!!!111

Oracle is not a toy. Oracle is a product used by professionals. Try to act like one.
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341646 is a reply to message #341600] Tue, 19 August 2008 15:01 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
rownum is the logical number of rows returned at the time of return.

rownum < 10 means return the first 9 rows.

rownum = 3 will never be true and will never return anything.

rownum > 10 will never return anything.

check out the analytic row_number for a way to do what you want.
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341700 is a reply to message #341646] Wed, 20 August 2008 00:41 Go to previous messageGo to next message
sumanthd
Messages: 10
Registered: June 2008
Location: Hyderabad
Junior Member

HI
in the quiery
select deptno from dept where rownum=3


the rownum is assigned after the quiery has been executed .but it works with rownum=1 .because for the first record the rownum will be assigned as 1

BUT WHERE as

select deptno from dept where rownum<=3


it forms all the rows as in quiery and fetches the rows which are less than rownum 3 so u get 3 rows
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341780 is a reply to message #341600] Wed, 20 August 2008 05:18 Go to previous messageGo to next message
uguddu
Messages: 5
Registered: August 2008
Junior Member
Hi,

Oracle selects a row from a table and ROWNUM returns a number indicating the order in which Oracle selects the row from a table. Thus the first row returned by a query has ROWNUM value 1, second row returned by the query has a ROWNUM value 2 and so on.

in below qurey

select deptno from dept where rownum=3

You are geting no rows, This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1 is not equal to 3. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.

Thanks & Regards,
Avadhesh
Re: rownum gives no data found!!!!!!!!!!!!111 [message #341899 is a reply to message #341780] Wed, 20 August 2008 13:30 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
uguddu wrote on Wed, 20 August 2008 06:18
Hi,

Oracle selects a row from a table and ROWNUM returns a number indicating the order in which Oracle selects the row from a table.



Just to be clear here, this is inaccurate.
FOO SCOTT>l
  1  select rownum, codenum from temp1
  2  where rownum < 20
  3* order by codenum
FOO SCOTT>/

    ROWNUM    CODENUM
---------- ----------
         1       2764
         4       2764
         3       2764
         2       2764
         9       2765
         8       2765
         7       2765
         6       2765
         5       2765
        10       2765
        11       2765
        12       2766
        13       2766
        14       2766
        15       2766
        16       2766
        17       2766
        18       2767
        19       2767

19 rows selected.

[Updated on: Thu, 21 August 2008 07:44]

Report message to a moderator

Re: rownum gives no data found!!!!!!!!!!!!111 [message #341997 is a reply to message #341899] Thu, 21 August 2008 00:58 Go to previous messageGo to next message
uguddu
Messages: 5
Registered: August 2008
Junior Member

In oracle ordering perform after fetching of the data ...

You have used order by clause in query, when whole data is fetched out by the query then order by clause arrange it in proper order..

this is the reason in your query are not getting rownum in sequense...

run your qurey without order by you will get records in prpper sequenceing..

Thanks & regards,
Avadhesh
Re: rownum gives no data found!!!!!!!!!!!!111 [message #342024 is a reply to message #341997] Thu, 21 August 2008 01:54 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In oracle ordering perform after fetching of the data ...

This is wrong. If you already fetched the data how could Oracle can change the order of what you will fetch?
Ordering is done at the last step of execution, BEFORE any fetch.

Regards
Michel
Previous Topic: Need Help on average and sort
Next Topic: I Need Select Query....
Goto Forum:
  


Current Time: Mon Dec 05 03:10:38 CST 2016

Total time taken to generate the page: 0.05428 seconds