| sql [message #19687] |
Wed, 03 April 2002 19:00  |
balagopal
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
How to select top 5 salary earners from a table ?
Eg:- table EMP
|
|
|
|
| Re: sql [message #19688 is a reply to message #19687] |
Wed, 03 April 2002 19:20  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10;
Use this workaround with prior releases:
SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)FROM my_table b WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;
|
|
|
|
| Re: sql [message #19723 is a reply to message #19687] |
Thu, 04 April 2002 17:34 |
Dinesh
Messages: 31
Registered: October 2001
|
Member |
|
|
Hi,
From oracle 8i onwards you have analytic functions
and you can use Rank() or Dense_Rank() to get the the ranks of your data..
you can use this query
select emp_name,emp_no,basic_salary,dense_Rank()
over ( order by basic_salary desc)
from employee
this will give you the top ranks
Let me know if you still have some doubts
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
