How to apply this query to all the employees

I have a query to compute the 3rd highest salary for an employee as shown below.

SELECT MAX(salary) 
FROM   employee 
WHERE  emp_no = 1 
  AND  salary < (SELECT MAX(salary) 
                 FROM   employee 
                 WHERE  emp_no = 1 
                   AND  salary NOT IN (SELECT MAX(salary) 
                                       FROM   employee
                                       WHERE  emp_no = 1)) 

How can I apply this query to give the 3rd highest salary for each employee which can be fetched by the query

select distinct(emp_no) 
from employee

Note: without using special functions like dense_rank()

sample table 
--------------------
EMP     SALARY
--------------------
1        1000
1        1000
1        900
1        800--->Required
2        1000
2        1000
2        500
2        400---->Required

Answer

You are looking for each employee’s third highest salary. It can happen that we find the same salary for an employee multiple times in the table, as your sample data shows. So, make the salaries per employee distinct, then rank the rows with ROW_NUMBER, RANK or DENSE_RANK (which doesn’t matter with distinct values) and then pick the third ranked.

select emp_no, salary
from
(
  select distinct
    emp_no,
    salary,
    dense_rank() over (partition by emp_no order by salary desc) as rnk
  from employee
) ranked
where rnk = 3
order by emp_no, salary;

An alternative would be to count higher salaries in a subquery and select those salaries where exist two higher salaries for the employee:

with distinct_salaries as 
(
  select distinct emp_no, salary from employee
)
select *
from distinct_salaries
where
( 
  select count(*)
  from distinct_salaries higher
  where higher.emp_no = distinct_salaries.emp_no
  and higher.salary > distinct_salaries.salary
) = 2;

Demo: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1e17669870f2e9c7f5867bf2ee6c24bf