Re: How to get the first 15 records of an Oracle query ?

Hi,

here are some solutions from Jurij Modic <jmodic_at_src.si> posted just a few days ago:

>hi:
>
>Suppose I have a table contains 10000 employee's names and their salaries.
>Who can come out a query can get the 1000 highest salary employees?

Couple of possible ways to do it...

1. (Caution - might be extremely slow on large tables) SELECT * FROM emp e1 WHERE 1000 >= (SELECT COUNT(sal) FROM emp e2 WHERE e2.sal >= e1.sal) ORDER BY e1.sal DESC;
2. (Much faster, for Oracle7.2 and higher) SELECT emp.* FROM emp, (SELECT rowid x, -1*sal FROM emp GROUP BY -1*sal, rowid) e2 WHERE emp.rowid = e2.x AND rownum <= 1000 ORDER BY emp.sal DESC;
3. (The fastest of all three) SELECT /*+ RULE */ emp.* FROM emp, dual WHERE -1*emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL) AND rownum <= 1000 ORDER BY emp.sal DESC;

Albert FEDIDA schrieb:
>
> I wonder if it is possible in SQL Oracle to get the first 15 records
> for a query?
> With MS-Access, it's as simple as :
>
> SELECT TOP 15 FROM TABLE;
>
> Thanks for your help.
>
> Albert

--

Regards

Matthias Gresz :-)

--
Matthias.Gresz_at_Privat.Post.DE

          /|
         / |        
        /| |\
       /||  |\
      / O    |\         
     |        |\ Galopping Horse beats Running Man.
    /          |\
   /      /     |\
  /    __/|      |\
  \°   /  |       |\
   \/_/   |        |\