Re: Select top 5 records

Examples of  top 5 record queres can be found at :
http://www.akadia.com/services/ora_analytic_functions.html#Top-N%20Queries

The parent directory,
http://www.akadia.com/services/ora_analytic_functions.html
explains other powerful new analytical functions of Oracle 8i and 9i.

Here's an example.

I found it easier to understand when starting at the parent, and then working
my way
through the documentation until I hit the Top-N Queries functions.

It's a lot of bang for the buck!.

Lloyd Muccio

set autotrace on explain
break on deptno skip 1

SELECT * FROM (
  SELECT deptno, ename, sal, ROW_NUMBER()
  OVER (
    PARTITION BY deptno ORDER BY sal DESC
  ) Top3 FROM emp
)
WHERE Top3 <= 3
/

    DEPTNO ENAME             SAL       TOP3
---------- ---------- ---------- ----------
        10 KING             5000          1
           CLARK            2450          2
           MILLER           1300          3

        20 SCOTT            3000          1
           FORD             3000          2
           JONES            2975          3

        30 BLAKE            2850          1
           ALLEN            1600          2
           TURNER           1500          3

9 rows selected.



"Samir Chitkara , Gurgaon" wrote:

> hi!
>
> can any one give me a solution to find of top 5 record in a table ; without
> using rownum / order by .
>
> Thanks
>
> Samir
>
> -----Original Message-----
> From: manit_jain [mailto:[email protected]]
> Sent: Monday, March 31, 2003 12:51 PM
> To: Oracle
> Subject: [oracle] Re: How can I Export/Import a single or multiple
> tables from/to Oracle 8 for windows95?
>
> the ORACLE_SID is the name of the oracle service you are using.if you are
> using Oracle8.0 then it would be orcl.Also in order to run imp/exp the
> classpath must be set to
>
> %ORACLE_HOME%/bin directory. if it is not set then
>
> go to prompt c:\orawin95\bin:\&gt; then type exp.
>
> also to export/import you must log on with DBA previleges.try it with
> system/manager account or grant the DBA role to the user through which you
> want to import the data.
>
> regards.
>
>
> Get Your Private, Free E-mail from Indiatimes at
> http://email.indiatimes.com
> Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com
> Bid for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to
> http://airsahara.indiatimes.com and Bid Now !
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or
> to unsubscribe send a blank email to %%email.unsub%%.
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or
> to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].