Improve company productivity with a Business Account.Sign Up

x
?
Solved

Limit number of results returned from a query

Posted on 2002-04-30
9
Medium Priority
?
3,160 Views
Last Modified: 2011-09-20
Is there a way to limit the number of results returned from any sql query?

eg. We have 1000 records in table A. A query we run on table A returns 350 results but we only want the first 50 of the results returned.

Thanks,

Patterson
0
Comment
Question by:Patterson
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Expert Comment

by:pennnn
ID: 6981936
You can use rownum, to limit the number of records returned. Example:
select * from emp
where rownum < 11;

That will return the first 10 records.
Please keep in mind that Oracle doesn't guarantee the order in which the records will be returned, so you will not know which 10 records will be returned.
If you use ORDER BY in your query it will be applied AFTER the rownum condition is applied, i.e. it will return 10 rows and then it will order them.
Hope that helps!
0
 
LVL 11

Expert Comment

by:pennnn
ID: 6981941
So in your case you need to add additional condition to your query and it will be something like that:
SELECT <column_list>
FROM A
WHERE <your_current_conditions>
AND rownum < 51;
0
 
LVL 3

Author Comment

by:Patterson
ID: 6982022
I thought of using ROWNUM however if my query returns a subset of data from the table the rownums have no sequence to them...

eg.

102
5567
12231
2219

I'm looking for a way to possibly get for example on the first 2 of those results.

Thanks,

Patterson
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 11

Accepted Solution

by:
pennnn earned 400 total points
ID: 6982087
I'm not sure that I understand exactly your requirement.
Here are several possible solutions:
1. You need to just the 50 records from the result set, no matter which
Then you can use the query above (using rownum < 51).
2. You need to get the first 50 records which are ordered by sertain criteria.
Then you can use the following query:
SELECT * FROM
(SELECT <your_query_here>....
 ORDER BY <whatever you need to order it by>)
WHERE rownum < 51;

The inner query will select whatever you need to select and will order the result set. The outer query will limit the result set to 50.
3. You need to get a sample from your table and then apply your condition on the sample.
You can use the SAMPLE construction. Example:
select * from <table>  SAMPLE  <sample_percent>;

If you can give a more detailed description of what exactly you need to do, that will certainly help!
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6982188
Analytic Functions offer a useful alternative:

select *
from (select  ename,
dense_rank() over
(order by ename
nulls last) dr
from emp)
where dr < 5

dense_rank() is powerful and fast.

Eric
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6982199
explanation:

Below is an Inline View (subquery)
that compares the "pseudo column" dr
against the result set of the parent query.
"dr" is a variable for the dense_rank() Analytic Function.


Tom Kyte explains this stuff:

http://asktom.oracle.com/pls/ask/FLOWS.wwv_flow.accept

here's the output:

select *
from (select  ename,
dense_rank() over
(order by ename
nulls last) dr
from emp)
where dr < 5


15:32:00 sys@DELLGX1P>/

ENAME              DR
---------- ----------
ADAMS               1
ALLEN               2
BLAKE               3
CLARK               4

Eric
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6982217
dr is a pseudo columns for the expression:

dense_rank() over
(order by ename
nulls last)

... i understand this looks funky, but w/ 8i Oracle offers 25 Analytic Functions to speed long queries.  These tools offer many creative possibilities, and are commonly used with InLine Views (subqueries).

There is a tutorial/download on this subject from the Feb 2002, LAOUG.org conference.

Eric.
0
 
LVL 3

Author Comment

by:Patterson
ID: 6983190
Thanks that did exactly what I needed it to do. For some reason I thought the rownum for each record was specific to the table as opposed to being specific to the result set which is why I had those wierd numbers in my original post.

I'm making a new Question with some points for ORACLETune because from my previous question I was looking for a site with a tutorial on analytic functions.

Thanks,

Patterson
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question