• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3161
  • Last Modified:

Limit number of results returned from a query

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
Patterson
Asked:
Patterson
  • 4
  • 3
  • 2
1 Solution
 
pennnnCommented:
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
 
pennnnCommented:
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
 
PattersonAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pennnnCommented:
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
 
ORACLEtuneCommented:
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
 
ORACLEtuneCommented:
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
 
ORACLEtuneCommented:
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
 
PattersonAuthor Commented:
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
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

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now