How to find out 2nd highest salary of employees?

Question

A created table named geosalary with the columns name, id, and salary:

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

I attempted this following code to find the 2nd highest salary:

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

But, getting this below error message:

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

What is wrong with my code?

Answer 1

I believe the error message is very 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

Willing to Learn SQL? Sign up for the SQL Certification course by Intellipaat.