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?.
CREATE TABLE mytable(
ForeignKey INTEGER NOT NULL
,Product VARCHAR(18) NOT NULL
,Manufacturing VARCHAR(23) NOT NULL
,Completed VARCHAR(33)
);
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (01,'Shoes','Step A','2020-02-24 00:00:00.0000000');
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (02,'Shirt','Step A','2020-02-25 00:00:00.0000000');
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (03,'Pants','Step A','2020-02-25 00:00:00.0000000');
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (01,'Shoes','Step B','2020-02-24 13:56:00.0000000');
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (02,'Shirt','Step B',NULL);
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (03,'Pants','Step B','2020-02-25 13:11:00.0000000');
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (04,'Hat','Step B',NULL);
INSERT INTO mytable(ForeignKey,Product,Manufacturing,Completed) VALUES (04,'Hat','Step A',NULL);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select *
from (
select
t.*,
rank() over(
partition by foreignkey
order by case when completed is null then 0 else 1 end, completed desc, manufacturing) rn
from mytable t
) t
where rn = 1
order by foreignkey
| foreignkey | product | manufacturing | completed | rn |
|---|---|---|---|---|
| 1 | Shoes | Step B | 2020-02-24 13:56:00.0000000 | 1 |
| 2 | Shirt | Step B | null | 1 |
| 3 | Pants | Step B | 2020-02-25 13:11:00.0000000 | 1 |
| 4 | Hat | Step A | null | 1 |