highest 3 values

[abhaysk]

Originally posted by jmodic
Try any of the following three examples (ordered from slowest to fastest):

SELECT /*+ RULE*/ ename, sal FROM scott.emp e1
WHERE 3 >= 
        (SELECT COUNT(sal)
         FROM scott.emp e2
         WHERE e2.sal >= e1.sal)
ORDER BY e1.sal DESC;



SELECT /*+ RULE*/ emp.ename, emp.sal
FROM scott.emp,
     (SELECT rowid x, -1*sal FROM scott.emp
      GROUP BY  -1*sal, rowid
     ) e2
WHERE emp.rowid = e2.x AND rownum <= 3
ORDER BY emp.sal DESC;

SELECT /*+ RULE*/ emp.ename, emp.sal FROM emp, dual
WHERE -1*emp.sal =   
      DECODE(dual.dummy(+),'X',NULL,NULL)
AND rownum <= 3
ORDER BY emp.sal DESC

Jurij :

I wonder if this/any would work if max value of SAL is spread/duplicated.

Abhay.