Solved

Select the TOP n Record From oracle

Posted on 2004-09-30
7
3,092 Views
Last Modified: 2008-01-09

Hi

How we can achieve the functionality of  SELECT "TOP N"  Records in ORACLE 8.1.7

-Edward
0
Comment
Question by:edwardsearch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 33

Accepted Solution

by:
sajuks earned 100 total points
ID: 12196942
Select * from table where rownum <= N;
0
 
LVL 2

Expert Comment

by:CallumT
ID: 12197056
If you mean TOP N records in realtion to rank then for example, to find the tables in your schema with the top 10 number of rows ;

SELECT table_name,num_rows
  FROM tabs A
  WHERE 10 >=
     (SELECT COUNT(*)
      FROM tabs B
      WHERE B.num_rows >= A.num_rows)
  ORDER BY num_rows DESC
/

returns :
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TS_MTU_EVENT                      1922411
TS_REQUEST                         238131
TS_MTU_REQUEST                     237825
OTA_DD_REQUEST_ORIG                145789
OTA_SIM                             10138
TS_SIM                              10138
SOPA_BATCH_KEY                      10126
TS_REG_EVENT                         2071
OTA_DD_REQUEST                       1142
TS_RESPONSE_PARAM                     506

0
 
LVL 15

Expert Comment

by:ishando
ID: 12197183
The usual statement would be

select * from
  (select cols_of_interest
   from table
   order by sort_cols [desc|asc])
where rownum <= N;
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 48

Expert Comment

by:schwertner
ID: 12197737
This is an example over scott/tiger schema.
It selects the top 3 records ordered by the column empno:

select empno, ename , ROWNUMB
from (select empno, ename , row_number() over (order by empno asc) as ROWNUMB
        from emp) res,
     (select count(*) as c from emp) e
 where ROWNUMB > e.c -3;
0
 
LVL 6

Expert Comment

by:morphman
ID: 12199032
There is another function called RANK which would seem more appropriate to use here.

select empno, ename , RNK
from (select empno, ename , rank() over (order by empno asc) as RNK
        from emp)
 where RNK <= 3;
0
 
LVL 3

Author Comment

by:edwardsearch
ID: 12210626
Thanks.  sajuks answer is sufficient to this casse
- Edward
0
 
LVL 6

Expert Comment

by:morphman
ID: 12210808
Beware of rownum. You cant order records and use rownum, so you cant get "top N " records, you can only get "First N records"...
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

738 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