Solved

Select the TOP n Record From oracle

Posted on 2004-09-30
7
3,091 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
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
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 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

828 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