highest 3 values

[amolik]

Hi

I was searching for highest 3 values finding query on forum. All I could found was query using ORDER BY in the INNER SELECT query. However I am on Oracle 8.0.5 where it is not working.
There is an error whenever I put ORDER BY inside INNER SELECT in FROM clause. Is there any other way to that.

I would like to make global query which can be run on any table for a specified column.

Amol

PS The query below gives error in my Oracle

select sal
from (select sal from emp order by sal desc)
where rownum <= 3;

from (select sal from emp order by sal desc)
*
ERROR at line 2:
ORA-00907: missing right parenthesis

[pando]

order by in inline view does NOT work in Oracle 8

[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

[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.

[jmodic]

Originally posted by abhaysk
Jurij :

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

Shure, they wil ALL work, why shouldn't they? (Hovewer the results you get from them might not be exactly what you have inspected ).

Anyhow, the original question is too general to be answered exactly - it must have been much more precise. We don't know exactly whar he wants. So even if it could be done with analytical functions, we still don't know which one of them to use: ROW_NUMBER(), RANK, DENSE_RANK ?

Anyway, to answer your question about the above queries and the "competing" values on the third position, let's say that the salaries in the sescending order are:

- EMP1: 10.000
- EMP2: 8.000
- EMP3: 5.000
- EMP4: 5.000
- EMP5: 5.000
- EMP6: 3.000

In this case the first query will list only EMP1 and EMP2, while the second and the third will list EMP1, EMP2 and *any ONE* of the remaining eployees that earn 5.000. So the second and the third query will alwways return three records, but in case of the equal salaries you dont know which one of those with the same rank will be displayed.

[slimdave]

With an index on the salary column, you can do ...

Select /*+ INDEX_DESC(e my_salary_index) */
   ename,
   sal
From
   scott.emp e
Where
   Rownum < 4;

It's very fast indeed, but you tun the risk that the optimizer might start ignoring the index_desc hint.

[abhaysk]

Originally posted by jmodic
Shure, they wil ALL work, why shouldn't they? (Hovewer the results you get from them might not be exactly what you have inspected ).

Anyhow, the original question is too general to be answered exactly -
it must have been much more precise.

Well ofcourse the Q isnt precise, but what i had feeling from this post was, he wanted ** TOP ** 3 salaries...

Originally posted by jmodic
Anyway, to answer your question about the above queries and the "competing" values on the third position, let's say that the salaries in the sescending order are:

- EMP1: 10.000
- EMP2: 8.000
- EMP3: 5.000
- EMP4: 5.000
- EMP5: 5.000
- EMP6: 3.000

In this case the first query will list only EMP1 and EMP2, while the second and the third will list EMP1, EMP2 and *any ONE* of the remaining eployees that earn 5.000. So the second and the third query will alwways return three records, but in case of the equal salaries you dont know which one of those with the same rank will be displayed.

Assume data is some thing like this

- EMP1: 10.000
- EMP2: 10.000
- EMP3: 10.000
- EMP4: 8.000
- EMP5: 5.000
- EMP6: 3.000

Then ur query will return EMP1, 2, 3 with SAL=10...but according to subject & post of the poster, this is not he wanted...(i assume/guess)

Abhay.

[tamilselvan]

SLIMDAVE,
INDEX_DESC may not work correctly in Oracle 8. Even though it is a valid hint.
I think Jonathan Lewis pointed out the bug 3 or 4 years ago.

Tamil