Home » SQL & PL/SQL » SQL & PL/SQL » Question on ROWNUM.
Question on ROWNUM. [message #304630] Wed, 05 March 2008 22:56 Go to next message
rputtagunta
Messages: 5
Registered: March 2008
Location: Texas, USA
Junior Member
Is there a difference between

1) select * from emp where rownum <=5 and salary > 3000;

and

2) select * from
(select * from emp where salary > 3000 )
where rownum <=5;

In other words, let's say if I have 10 employees with salary>3000, will the first query ALWAYS give me 5 rows. I think the second query definitely will.

Thank you,
Rahul.
Re: Question on ROWNUM. [message #304638 is a reply to message #304630] Wed, 05 March 2008 23:40 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
No difference.

regards,
Re: Question on ROWNUM. [message #304640 is a reply to message #304630] Wed, 05 March 2008 23:45 Go to previous messageGo to next message
amardeep.sidhu
Messages: 7
Registered: October 2007
Junior Member
No Difference.

As in both the cases you will get 4 rows with salary>3000.

Sidhu
Re: Question on ROWNUM. [message #304641 is a reply to message #304638] Wed, 05 March 2008 23:47 Go to previous messageGo to next message
rputtagunta
Messages: 5
Registered: March 2008
Location: Texas, USA
Junior Member
Why not? Is there a link suggesting that? Could it be possible that the rownum happens first and then salary>3000 happens later?

What I meant to ask is for the first query. Can it be interpreted as below:

Take any 5 rows and then see if salary>3000.

I know that usually explain plan gives 'count stopkey' in the end, but, is that a guarantee?

Thank you,
Rahul.
Re: Question on ROWNUM. [message #304642 is a reply to message #304630] Thu, 06 March 2008 00:05 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Could it be possible that the rownum happens first and then salary>3000 happens later?

Not possible.

From ROWNUM section in documentation:
Quote:
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.


More detailed explanation is on AskTom in http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:948366252775 thread.

Just compare with
select *
from ( select *, rownum rn from emp )
where salary > 3000 and rn <= 5;


[Edit: Added last paragraph]

[Updated on: Thu, 06 March 2008 00:11]

Report message to a moderator

Previous Topic: ORA-01861: literal does not match format string
Next Topic: Does order by slow down searches
Goto Forum:
  


Current Time: Fri Dec 09 15:39:02 CST 2016

Total time taken to generate the page: 0.21029 seconds