sqlpostgresqlderived-table

How to find out 2nd highest salary of employees?

Created table named geosalary with columns name, id, and salary:

name   id  salary  
patrik  2  1000  
frank   2  2000  
chinmon 3  1300  
paddy   3  1700

I tried this below code to find 2nd highest salary:

SELECT salary
FROM (SELECT salary, DENSE_RANK() OVER(ORDER BY SALARY) AS DENSE_RANK FROM geosalary)
WHERE DENSE_RANK = 2;

However, getting this error message:

ERROR: subquery in FROM must have an alias  
SQL state: 42601  
Hint: For example, FROM (SELECT ...) [AS] foo.  
Character: 24

What's wrong with my code?

Solution

  • I think the error message is pretty clear: your sub-select needs an alias.

    SELECT t.salary 
FROM (
      SELECT salary,
          DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSE_RANK 
      FROM geosalary
      ) as t  --- this alias is missing
WHERE t.dense_rank = 2