How to apply this query to all the employees-CodePudding

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

CodePudding user response：

select * from (
select e.*, dense_rank() over(order by salary desc) rnk
  FROM employee e
)t where rnk = 3
limit 1;

The alternative way without using dense_rank:

    select  e.* 
      FROM employee e
       join (
         select min(salary) as salary from(
          select distinct salary 
            from employee e 
            order by salary desc
            limit 3
           )c
        ) t on t.salary = e.salary
       limit 1;

CodePudding user response：

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

CodePudding user response：

This seems possible with a lateral join:

select e.emp_no, x.salary
from (
  select distinct emp_no
  from employee
) e
  cross join lateral (
      select salary
      from employee e2
      where e2.emp_no = e.emp_no
      order by salary desc
      offset 3
      fetch first 1 row with ties
  ) x

Online example

But a window function would be much more efficient.

CodePudding user response：

without using special functions like dense_rank()

I think you cannot do this without analytic function, because database have to analyze data between rows and order it in memory for given sub-ordered results