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 files (file text, path text, created timestamp);
INSERT INTO files VALUES
('AAA', '08/22/A', '2022-08-22 22:00:00')
, ('AAA', '08/22/A', '2022-08-22 21:00:00')
, ('AAA', '08/21/A', '2022-08-21 20:00:00')
, ('AAA', '08/20/A', '2022-08-20 21:00:00')
, ('BBB', '08/22/B', '2022-08-22 21:00:00')
, ('CCC', '08/22/C', '2022-08-22 21:00:00')
, ('CCC', '08/21/C', '2022-08-21 21:00:00')
;
7 rows affected
WITH ranked_messages AS (
SELECT path
, row_number() OVER (PARTITION BY file ORDER BY max(created) DESC) AS rating_in_section
FROM files
GROUP BY file, path
)
SELECT path
FROM ranked_messages
WHERE rating_in_section > 1
GROUP BY path
ORDER BY path DESC;
| path |
|---|
| 08/21/C |
| 08/21/A |
| 08/20/A |