How to find the employee with the second highest salary?

by

The way to do this is with Oracle’s Analytic functions. Your particular scenario is just a variant on the solution I provided in another thread.

If you are interested in just selecting the second highest salary then any of DENSE_RANK(), RANK() and ROW_NUMBER() will do the trick:

SQL> select * from
  2   ( select sal
  3            , rank() over (order by sal desc) as rnk
  4     from
  5      ( select distinct sal
  6        from emp )
  7    )
  8  where rnk = 2
  9  /

       SAL        RNK
---------- ----------
      3000          2

SQL>

However, if you want to select additional information, such as the name of the employee with the second highest salary, the function you choose will affect the result. The main reason for choosing one over another is what happens when there is a tie.

If you use ROW_NUMBER() it will return the second employee ordered by salary: what if there are two employees tying for the highest salary? What if there are two employees tying for the second highest salary? Wheareas if you use RANK() and there are two employees tying for first highest salary, there will be no records with RANK = 2.

I suggest DENSE_RANK() is the usually the safest function to choose in these cases, but it really does depend on the specific business requirement.

More Related Contents:

  1. Oracle SELECT TOP 10 records [duplicate]
  2. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  3. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  4. The most efficient way to UPPER CASE two columns in a single query [closed]
  5. Current date in Oracle Db
  6. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  7. Case insensitive searching in Oracle
  8. Get value based on max of a different column grouped by another column [duplicate]
  9. MySQL equivalent of DECODE function in Oracle
  10. How to get Oracle create table statement in SQL*Plus
  11. How do I select all the columns from a table, plus additional columns like ROWNUM?
  12. SQL error “ORA-01722: invalid number”
  13. Disable all table constraints in Oracle
  14. ORA – 00933 confusion with inner join and “as”
  15. OVER clause in Oracle
  16. How to count the number of occurrences of a character in an Oracle varchar value?
  17. PARTITION BY with and without KEEP in Oracle
  18. Comparing with date in Oracle sql
  19. Is it possible to query a comma separated column for a specific value?
  20. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  21. SQL selecting rows by most recent date with two unique columns
  22. How to extract group from regular expression in Oracle?
  23. Using LIKE in an Oracle IN clause
  24. How to handle optional parameters in SQL query?
  25. What does a (+) sign mean in an Oracle SQL WHERE clause? [duplicate]
  26. How to insert a string which contains an “&”
  27. Connecting to Oracle Database using Sql Server Integration Services
  28. ORA-00933: SQL command not properly ended
  29. Update a table with data from another table
  30. Insert into from CTE

Leave a Comment