Third highest salary using rank function in Postgres Sql

Question

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)

I think the error message is hinting what to correct, you are missing `AS` before your alias `K` in the code `from (select emp_sal from emp_demo order by emp_sal desc))K`. Can you try if that helps? — dubes, Sep 07 '16 at 20:02

score 2 · Answer 1 · answered Sep 07 '16 at 17:01

2

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

answered Sep 07 '16 at 17:01

Mureinik

297,002
52
306
350

..Thanks for the answer.. actually i tried using dense_rank but result were not coming so i thought it would be better to start from basic that's why i used row_number().. still error was coming ...so i posted here the question.. thanks a Lot for logic....It working now.. – user2715085 Sep 07 '16 at 17:08

Vijay Anand Pandian · Answer 2 · 2020-05-05T18:16:56.883

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

Third highest salary using rank function in Postgres Sql

2 Answers2