Solved

Select the TOP n Record From oracle

Posted on 2004-09-30
7
3,085 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
Comment Utility
Select * from table where rownum <= N;
0
 
LVL 2

Expert Comment

by:CallumT
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks.  sajuks answer is sufficient to this casse
- Edward
0
 
LVL 6

Expert Comment

by:morphman
Comment Utility
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

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.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now