Solved

Limit number of results returned from a query

Posted on 2002-04-30
9
3,152 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Accepted Solution

by:
pennnn earned 100 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 5

Expert Comment

by:ORACLEtune
ID: 6982278
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

810 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