Top N Analysis

[varshanswamy]

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;

[DaPi]

Originally posted by varshanswamy
This query seems not to work.

How? Why? Error message? Wrong answer?

[varshanswamy]

the erroe i get is ERROR at line 1:
ORA-00907: missing right parenthesis

[hrishy]

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.

[varshanswamy]

thanx a lot!!!

[varshanswamy]

Hi hrishy

Can u explain the logic for the query esp the inner query.

bye.
Aparajita.

[jmodic]

Btw, the suggested method is the slowest of all possible pre-8i top-N query variations.

[slimdave]

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.

[hrishy]

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

[jmodic]

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