?
Solved

Select the TOP n Record From oracle

Posted on 2004-09-30
7
Medium Priority
?
3,094 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 300 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 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