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

# 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
• 4
• 3
• 2
1 Solution

Commented:
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

Commented:
SELECT <column_list>
FROM A
WHERE <your_current_conditions>
AND rownum < 51;
0

Author 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

Commented:
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

Commented:
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

Commented:
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:

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
---------- ----------
ALLEN               2
BLAKE               3
CLARK               4

Eric
0

Commented:
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

Commented:
0

Author 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.