Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> how to select first 100 rows from a table, how to select first 100 rows from a table
gopalk
post Aug 29 2007, 01:57 AM
Post #1


Newbie
*

Group: Members
Posts: 8
Joined: 29-August 07
Member No.: 11,534



hi,

could anyone help me with the query to select first 100 rows from a table?

Thanks,
Gopalk
Go to the top of the page
 
+Quote Post
crottykt
post Aug 29 2007, 02:22 AM
Post #2


Advanced Member
***

Group: Members
Posts: 180
Joined: 2-August 05
From: Raleigh, NC
Member No.: 2,640



See http://www.dba-oracle.com/oracle_select_top_ten_tips.htm


--------------------
Kent Crotty
Go to the top of the page
 
+Quote Post
gopalk
post Aug 29 2007, 02:31 AM
Post #3


Newbie
*

Group: Members
Posts: 8
Joined: 29-August 07
Member No.: 11,534



QUOTE (crottykt @ Aug 29 2007, 12:53 PM) *
select * from table
where rownum < 101



thanks crotty for quick reply biggrin.gif

gopalk
Go to the top of the page
 
+Quote Post
dave
post Aug 29 2007, 02:56 AM
Post #4


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



not really true, thats the first 100 rows of the result set
Go to the top of the page
 
+Quote Post
gopalk
post Aug 29 2007, 04:03 AM
Post #5


Newbie
*

Group: Members
Posts: 8
Joined: 29-August 07
Member No.: 11,534



QUOTE (dave @ Aug 29 2007, 01:27 PM) *
not really true, thats the first 100 rows of the result set

dave,

what do you mean by first 100 rows of result set?
I checked the query and found it gave me the right result. Please let me know if I am missing something.

Thanks,
Gopalk
Go to the top of the page
 
+Quote Post
dave
post Aug 29 2007, 04:07 AM
Post #6


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



it might well be right, but rows in a table dont have an order so you are just selecting any 100 rows that it chcuks out first.

That may be want you want, or you might be getting lucky
Go to the top of the page
 
+Quote Post
mbobak
post Aug 29 2007, 05:01 PM
Post #7


Advanced Member
***

Group: Members
Posts: 547
Joined: 25-May 05
From: Belleville, MI
Member No.: 2,186



I think what Dave is getting at is, what's your definition of "first 100 rows"? First in what sense? Ordered by some column value? Any 100 rows? If you want any 100 rows, then the query provided will work.

If however, you wanted, let's say, the top 100 employees with the highest salaries, then if you were to do:
CODE
select * from emp where rownum < 101;

that doesn't say anything about order by salary.

If you were to do:
CODE
select * from emp where rownum < 101 order by salary;

You wouldn't get an error, the above is syntactically correct, but, again, you wouldn't get what you expect.

Now, if you did:
CODE
select * from (select * from emp order by salary) where rownum <101;

Then you'd get the result you expected.

So, again, what do you mean by "the first 100 rows"?

Hope that helps,

-Mark


--------------------
--
Mark J Bobak
mark@bobak.net

Do you want good, clear answers to your questions?
Read THIS BEFORE posting!
Go to the top of the page
 
+Quote Post
dave
post Aug 29 2007, 06:04 PM
Post #8


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



and theres the difference between a good response and a terse short one
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 1st September 2014 - 08:49 PM