
highest 3 values
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:
ORA00907: missing right parenthesis

order by in inline view does NOT work in Oracle 8

Try any of the following three examples (ordered from slowest to fastest):
Code:
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 Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

Originally posted by jmodic
Try any of the following three examples (ordered from slowest to fastest):
Code:
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

With an index on the salary column, you can do ...
Code:
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.

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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
