By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table employee (emp_no int, salary int);
insert into employee(emp_no, salary)
values
(1,100), (1, 110), (1,100), (1,110), (1,120),
(2, 90), (2, 130),
(3,100), (3, 200), (3,300), (3,400), (3,500)
;
12 rows affected
select emp_no, salary
from
(
select distinct
emp_no,
salary,
row_number() over (partition by emp_no order by salary desc) as rnk
from employee
) ranked
where rnk = 3
order by emp_no, salary;
| emp_no | salary |
|---|---|
| 1 | 110 |
| 3 | 300 |
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;
| emp_no | salary |
|---|---|
| 1 | 100 |
| 3 | 300 |