-
Top N Analysis
i want to find out top 5 salaries of employees.
I am working with Oracle 8.0.5. This query seems not to work. Can u give me another solution to this query. I dont want to achieve the result through a cursor.
select sal from ( select distinct sal from emp order by sal desc ) where rownum <= 5;
-
Re: Top N Analysis
Originally posted by varshanswamy
This query seems not to work.
How? Why? Error message? Wrong answer?
-
the erroe i get is ERROR at line 1:
ORA-00907: missing right parenthesis
-
Hi
1 select empno,sal
2 from emp e
3 where 5>(select count(*) from emp
4 where sal>e.sal)
5* order by sal desc
SQL> /
EMPNO SAL
---------- ----------
7839 5000
7788 3000
7902 3000
7566 2975
7698 2850
SQL> set pagesize 100
SQL> select empno,sal from emp
2 order by sal desc
3
SQL> /
EMPNO SAL
---------- ----------
7839 5000
7788 3000
7902 3000
7566 2975
7698 2850
7782 2450
7499 1600
7844 1500
7934 1300
7521 1250
7654 1250
7876 1100
7900 950
7369 800
14 rows selected.
-
-
Hi hrishy
Can u explain the logic for the query esp the inner query.
bye.
Aparajita.
-
Btw, the suggested method is the slowest of all possible pre-8i top-N query variations.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by varshanswamy
the erroe i get is ERROR at line 1:
ORA-00907: missing right parenthesis
"order by" is not permitted in in-line views in 8.0.5, I believe.
-
Originally posted by varshanswamy
Hi hrishy
Can u explain the logic for the query esp the inner query.
bye.
Aparajita.
Hi Aparajita
The inner query is called co-releated sql query in Relation Database Terminology.A co-releated sql query executes once for the row returned by the main query.Look up the documentation or search google for co-releated subquery.
A ordinary subquery on the other hand is run once only
Hope the explanation was useful.
In 8.0.5 since analytical functions are not avaliable the above query is the way to go.
regards
Hrishy
-
Originally posted by hrishy
In 8.0.5 since analytical functions are not avaliable the above query is the way to go.
As I already said, this is the slowest of all possible top-n solutions for 8.0.5. Also you might be surprised that sometimes your query will not return 5 records (for top-5 example of yours), it might return you anything between 0 and 5 records. If for example on emp table you wanted two highest salaries, you would only get one with your query, i.e.
EMPNO SAL
---------- ----------
7839 5000
If you want to check some more efficient top-N possibilities in pre-8i, you might check this thread:
http://www.dbasupport.com/forums/sho...threadid=10310
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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
|