Re: A sample question

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 22 Sep 1998 21:23:17 GMT
Message-ID: <360812fe.3502358@news.siol.net>

On Tue, 22 Sep 1998 13:47:00 -0500, "Dong Tang" <tangd_at_ctcdist.com> wrote:

>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;

>Dong Tang

HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Sep 22 1998 - 16:23:17 CDT