Get highest 3 salary in Oracle 10g

Hello and Welcome back.



This is my first blog since I returned  to Egypt  as I was working in KSA for year and half.



Here's how to get the highest three salaries in HR Schema using SQL.



# Old method was using ROWNUM , but it's not accurate  because it returns the first 3 rows only using the following code :

SELECT   *

  FROM   (  SELECT   last_name, salary

              FROM   employees

          ORDER BY   salary DESC)

 WHERE   ROWNUM <= 3

here's what you  get :

LAST_NAME	SALARY
King	24,000
Kochhar	17,000
De Haan	17,000

But  using an other method using Dense_Rank() Over , returns results more accurate.

Here's the code:

SELECT   *

  FROM   (SELECT   last_name, salary,

                   DENSE_RANK () OVER (ORDER BY salary DESC) top_rank

            FROM   employees)

 WHERE   top_rank <= 3

here's what you get :

LAST_NAME	SALARY	TOP_RANK
King	24,000	1
Kochhar	17,000	2
De Haan	17,000	2
Russell	14,000	3

Thanks for  CodeProject Site