How to find out 2nd highest salary of employees?

sql postgresql derived-table
franky picture franky · Jul 12, 2013 · Viewed 30k times · Source

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?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Jul 12, 2013

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More