How to find out 2nd highest salary of employees?

Question

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?

SELECT salary FROM (SELECT salary ,DENSE_RANK() as DENSE_RANK OVER(ORDER BY SALARY) AS DENSE_RANK FROM geosalary) as dontcare WHERE DENSE_RANK=2; — Galigator, Jul 12 '13 at 07:05

score 10 · Accepted Answer · edited Sep 20 '18 at 15:48

10

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

edited Sep 20 '18 at 15:48

abhishek anand

35
1
8

answered Jul 12 '13 at 07:32

I am trying leetcode question https://leetcode.com/problems/nth-highest-salary/submissions/ and it returns runtime error - Subquery returns more than 1 row – Sarde Mar 08 '21 at 08:07
@TusharS: this query can not result in that error ("subquery returns more than one row") – Mar 08 '21 at 08:10

Erwin Brandstetter · Answer 2 · 2023-06-12T01:44:08.540

6

The error message is pretty obvious: You need to supply an alias for the subquery.

Alternative query:

SELECT DISTINCT salary
FROM   geosalary
ORDER  BY salary DESC NULLS LAST
OFFSET 1
LIMIT  1;

This finds the "2nd highest salary" (1 row) as requested. Other queries find all employees with the 2nd highest salary (1-n rows).

I added NULLS LAST, as null values typically shouldn't rank first for this purpose. See:

Sort by column ASC, but NULL values first?

edited Jun 12 '23 at 01:44

answered Jul 12 '13 at 07:34

Erwin Brandstetter

605,456
145
1,078
1,228

why this answer have lesser upvotes ? any specific reason ? – Nishant Nawarkhede Feb 11 '20 at 10:09
1

@NishantNawarkhede: Not sure. I added some explanation. – Erwin Brandstetter Feb 11 '20 at 13:49
This is simplest solution. – Nishant Nawarkhede Feb 11 '20 at 14:04

score 2 · Answer 3 · edited Nov 11 '14 at 20:04

SELECT department_id, salary, RANK1 FROM (
    SELECT department_id,
           salary,
           DENSE_RANK ()
              OVER (PARTITION BY department_id ORDER BY SALARY DESC)
              AS rank1
    FROM employees) result  
WHERE rank1 = 3

This above query will get you the 3rd highest salary in the individual department. If you want regardless of the department, then just remove PARTITION BY department_id

score 0 · Answer 4 · answered Jul 12 '13 at 07:06

0

Your SQL engine doesn't know the "salary" column of which table you are using, that's why you need to use an alias to differentiate the two columns. Try this:

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

answered Jul 12 '13 at 07:06

glautrou

3,140
2
29
34

Read the error message again, your statement would still throw the same error. – Jul 12 '13 at 07:29

score 0 · Answer 5 · answered Jul 12 '13 at 07:09

0

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

answered Jul 12 '13 at 07:09

cathulhu

641
1
9
20

score 0 · Answer 6 · answered Jul 14 '13 at 17:11

0

select  level, max(salary)
from   geosalary
where   level=2
connect  by
prior   salary>salary
group  by  level;

answered Jul 14 '13 at 17:11

Vimal

11
1

anieshaz · Answer 7 · 2017-05-22T07:54:12.857

0

In case of duplicates in salary column below query will give the right result:

WITH tmp_tbl AS
  (SELECT salary,
    DENSE_RANK() OVER (ORDER BY SALARY) AS DENSE_RANK
  FROM geosalary
  )
SELECT salary
FROM tmp_tbl
WHERE dense_rank =
  (SELECT MAX(dense_rank)-1 FROM tmp_tbl
  )
AND rownum=1;

edited May 22 '17 at 07:54

answered May 21 '17 at 16:40

anieshaz

1
2

score 0 · Answer 8 · answered May 06 '19 at 06:19

0

Here is SQL standard

SELECT name, salary
FROM   geosalary 
ORDER BY salary desc
OFFSET 1 ROW
FETCH FIRST 1 ROW ONLY

To calculate nth highest salary change offset value

answered May 06 '19 at 06:19

Kuldeep S.

11
5

score -1 · Answer 9 · answered Jul 12 '13 at 07:06

-1

SELECT MAX(salary) FROM geosalary WHERE salary < ( SELECT MAX(salary) FROM geosalary )

answered Jul 12 '13 at 07:06

Dhrubajyoti Gogoi

1,265
10
18

How to find out 2nd highest salary of employees?

9 Answers9