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;
Can u explain the logic for the query esp the inner query.
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.
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.