CODEWITHSUNDEEP
sqlpostgresqlselecttop-n
user2715085
user2715085

Reputation: 93

Third highest salary using rank function in Postgres Sql

Query for third highest salary using rank function 

select * 
from (
    select emp_sal,row_number()over() as RANK 
    from (
       select emp_sal 
       from emp_demo 
       order by emp_sal desc
    )
)K 
where K.RANK=3

error coming as 

*ERROR:  subquery in FROM must have an alias
LINE 1: ...m (select emp_sal,row_number()over() as RANK from (select em...
                                                             ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
********** Error **********
ERROR: subquery in FROM must have an alias
SQL state: 42601
Hint: For example, FROM (SELECT ...) [AS] foo.
Character: 63*

I am not able to display third highest salary in RazorSQL (also in Postgresql)

Upvotes: 2

Views: 5295

Answers (2)

Vijay Anand Pandian
Vijay Anand Pandian

Reputation: 1175

Below works 

select *  from employee emp1 where (2) = (select count(distinct(emp2.salary))
         from employee emp2 where emp2.salary > emp1.salary )

the query works like a for a loop when the count is 0 means, it is the top highest maximum value.

when the count is 1, there is only one highest value greater than the current value. so it should be the second highest.

when the count is 2, there are two values greater than the current value. so it should be the third highest.

Alternative

select distinct(salary) as salary from employee order by salary desc offset 2 limit 1

Upvotes: 2

Mureinik
Mureinik

Reputation: 311998

You seem to be missing the contents of the over clause. Also, you should probably be using dense_rank and not row_number in case several employees have the same salary:

SELECT *
FROM   (SELECT *, DENSE_RANK() OVER (ORDER BY emp_sal DESC) AS rk 
        FROM   emp_demo) t
WHERE  rk = 3

Upvotes: 2

Related Questions