Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3102
  • Last Modified:

Select the TOP n Record From oracle


Hi

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

-Edward
0
edwardsearch
Asked:
edwardsearch
1 Solution
 
sajuksCommented:
Select * from table where rownum <= N;
0
 
CallumTCommented:
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
 
ishandoCommented:
The usual statement would be

select * from
  (select cols_of_interest
   from table
   order by sort_cols [desc|asc])
where rownum <= N;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
schwertnerCommented:
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
 
morphmanCommented:
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
 
edwardsearchAuthor Commented:
Thanks.  sajuks answer is sufficient to this casse
- Edward
0
 
morphmanCommented:
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
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now