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 12.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table instructor_schedule(
instructor_schedule_id bigserial primary key,
instructor_name varchar(255),
course_name varchar(255),
start_date timestamp with time zone
)
alter table instructor_schedule add constraint no_dupes unique(instructor_name, course_name, start_date)
insert into instructor_schedule (instructor_name, course_name, start_date) values
('bob', 'databases', '2015-01-01 00:00:00.000000 +00:00'),
('bob', 'databases', '2018-01-01 00:00:00.000000 +00:00'),
('bob', 'databases', '2024-01-01 00:00:00.000000 +00:00'),
('alice', 'databases', '2021-01-01 00:00:00.000000 +00:00'),
('alice', 'databases', '2022-01-01 00:00:00.000000 +00:00');
5 rows affected
with data
as (
select *,row_number() over(partition by instructor_name,course_name order by start_date desc) as rnk
from instructor_schedule
where start_date<=now() --restrict records to entries which have occured in the past
)
select *
from data x
where x.rnk=1 --as we are ranking using the earliest start_date rnk=1, gets you the latest entry record.
| instructor_schedule_id | instructor_name | course_name | start_date | rnk |
|---|---|---|---|---|
| 5 | alice | databases | 2022-01-01 00:00:00+00 | 1 |
| 2 | bob | databases | 2018-01-01 00:00:00+00 | 1 |
select orig.*
from instructor_schedule orig
join (select max(start_date) as max_start_date,
course_name,
instructor_name
from instructor_schedule as is1
where start_date<=now()
group by course_name,
instructor_name
) inner1
on orig.course_name=inner1.course_name
and orig.instructor_name=inner1.instructor_name
and orig.start_date=inner1.max_start_date
| instructor_schedule_id | instructor_name | course_name | start_date |
|---|---|---|---|
| 2 | bob | databases | 2018-01-01 00:00:00+00 |
| 5 | alice | databases | 2022-01-01 00:00:00+00 |