Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Limit number of results returned from a query

Posted on 2002-04-30
9
Medium Priority
?
3,158 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

972 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