Re: Is there anything in oracle similar to top in sql server 2000?

From: Sagi <sag1rk_at_yahoo.com>
Date: 30 Oct 2002 09:09:17 -0800
Message-ID: <54d80104.0210300909.294ad00_at_posting.google.com>

sohelcsc_at_yahoo.com (Leader) wrote in message news:<b1a93c73.0210230408.ad15189_at_posting.google.com>...
> Hi All,
>
> I have a question? that is in sql server and access we use top to get
> top results from a table.. like
> select top 10 customer from customer_Table.
>
> in oracle what can i use for the above statement???
>
>
> Thanx in Advance
> Hoque

Hi,

I hope you must be aware of EMP table in Oracle. If not this is the Table definition for your ready reference:

SQL> desc emp

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Now say i want to get the employee information for the TOP 5 Salaries. The query would be

select * from
(SELECT A.*,
DENSE_RANK() OVER (ORDER BY SAL DESC) DR FROM EMP A)
WHERE DR <= 5

Output:

---

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM 
   DEPTNO         DR

----- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------

 7839 KING       PRESIDENT            17-NOV-81       5000            
       10          1
 7788 SCOTT      ANALYST         7566 09-DEC-82       3000            
       20          2
 7902 FORD       ANALYST         7566 03-DEC-81       3000            
       20          2
 7566 JONES      MANAGER         7839 02-APR-81       2975            
       20          3
 7698 BLAKE      MANAGER         7839 01-MAY-81       2850            
       30          4
 7782 CLARK      MANAGER         7839 09-JUN-81       2450            
       10          5

Regards,
Sagi Received on Wed Oct 30 2002 - 18:09:17 CET