My point is this:
If you're defining "first n" as "any n", then qualifying the ROWNUM value
works just fine.
If you're defining "first n" as "the first n of an ordered set", then it's
not guaranteed to work.
A simple test on the standard emp table will illustrate this (well, it did
on the standard
emp table in earlier versions, I haven't tried it in our 10g database).
Since we don't have it,
I used a sort by table_name on user_tables to illustrate it.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"Edwards Ed "
<oracledba-ezmlms
hield-x63145169.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
11/29/2006 02:00
PM Subject
RE: need a query or procedure..
Hmmmmmmm! Hehe.
-----Original Message-----
From: Joel
[mailto:oracledba-ezmlmshield-x47056135.[Email address protected]
Sent: Wednesday, November 29, 2006 1:36 PM
To: LazyDBA Discussion
Subject: RE: need a query or procedure..
However, you have change the terms of the question now and added stuff.
Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546
-----Original Message-----
From: William J Koster
[mailto:oracledba-ezmlmshield-x27631315.[Email address protected]
Sent: Wednesday, November 29, 2006 1:29 PM
To: LazyDBA Discussion
Subject: RE: need a query or procedure..
No, they're not all the same. As soon as you want to apply an ordering
to
the result, the first example
with the simple WHERE clause with ROWNUM fails. Try it with emp or, as
I
did, since we don't have
emp defined, with user_tables. If you want the first "n" of an ordered
set
of rows, the third example
is fine, but the first returns the same set as though you hadn't even
specified the ordering -- which is the
way it's supposed to work.
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit
written
agreement or government initiative expressly permitting the use of
e-mail
for such purpose.
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------
"Joel"
<oracledba-ezmlms
hield-x64092764.x
To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
11/29/2006 11:15
AM
Subject
RE: need a query or procedure..
Did I miss something? Are these three not the same?
Select col1,col2 from tab1 where rownum <= 1000
Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546
select * from
(select empno, ename, job, mgr, hiredate, sal, comm, deptno,
rank() over (order by rownum asc) ranking
from emp)
where ranking <= 1000
/
select rownumber, deptno, empno
from(
select row_number() over(order by deptno) as rownumber, deptno, empno
from emp) t where rownumber <= 10 /
Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Dustin Hayden
[mailto:oracledba-ezmlmshield-x35172397.[Email address protected]
Sent: Wednesday, November 29, 2006 9:16 AM
To: LazyDBA Discussion
Subject: RE: need a query or procedure..
SELECT /*+ FIRST_ROWS(N) */ ....
-----Original Message-----
From: Strelzyk Stephen
[mailto:oracledba-ezmlmshield-x24012264.[Email address protected]
Sent: Wednesday, November 29, 2006 8:53 AM
To: LazyDBA Discussion
Subject: RE: need a query or procedure..
Select col1,col2 from tab1 where rownum <= 1000
-----Original Message-----
From: David NGUYEN
[mailto:oracledba-ezmlmshield-x93988314.[Email address protected]
Sent: Tuesday, November 28, 2006 6:54 AM
To: LazyDBA Discussion
Subject: need a query or procedure..
hello experts,
i have a table tab1(col1,col2) , i just need to get the first 1000 rows
in this table , how to write this query ou procedure.?
thanks in advance.
david..
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
Oracle LazyDBA home page