Re: SQL problem
Date: Sat, 27 Jun 1998 22:18:22 GMT
Message-ID: <359565b6.5156690_at_www.sigov.si>
On Sat, 27 Jun 1998 13:47:01 GMT, lasttrap_at_qatar.net.qa wrote:
>I need To Find The first 3 highest salary in emp table without replication.
The solution provided by Boris doesn't return the result you want (the clasical problem with ROWNUM applied to an unsorted resultset).
The solution provided by Thomas works as you wanted it (to get also the ename along with the salary you should modify it a little), but don't run it on a larger table, as it would take forewer to finish (on 20.000 rows table I terminated it after about 20 minutes).
Here is a sulution that returns the result in less then 10 seconds on the same 20.000 rows table:
SELECT b.ename, -1*b.sal sal FROM
(SELECT MIN(a.ename) ename, -1*a.sal sal FROM
(SELECT ename, MAX(sal) sal FROM emp GROUP BY ename) a
GROUP BY -1*a.sal) b
WHERE rownum < 4;
The reason for better performance with this one is that it performs only one full table scan, without any FILTER or NESTED LOOP operations.
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Jun 28 1998 - 00:18:22 CEST