Window function with same result in subgroup

Question

Based on this table:

file	path	created
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

I have the following query in PostgreSQL:

WITH ranked_messages AS (
    select file, created, path,
           row_number() OVER (PARTITION BY file ORDER BY created DESC)  AS rating_in_section
    from files
    order by file
)
SELECT path FROM ranked_messages WHERE rating_in_section > 1 group by path order by path desc;

But results are not what I want:

path
08/22/A
08/21/C
08/21/A
08/20/A

http://sqlfiddle.com/#!15/3bc6a/1

I really want this:

path
08/21/C
08/21/A
08/20/A

The window function marks data without group by path. The intermediary state is:

file	path	created	raiting
AAA	08/22/A	2022-08-22 22:00:00	1
AAA	08/22/A	2022-08-22 21:00:00	2
AAA	08/21/A	2022-08-21 20:00:00	3
AAA	08/20/A	2022-08-20 21:00:00	4
BBB	08/22/B	2022-08-22 21:00:00	1
CCC	08/22/C	2022-08-22 21:00:00	1
CCC	08/21/C	2022-08-21 21:00:00	2

But I need:

file	path	created	raiting
AAA	08/22/A	2022-08-22 22:00:00	1
AAA	08/22/A	2022-08-22 21:00:00	1
AAA	08/21/A	2022-08-21 20:00:00	2
AAA	08/20/A	2022-08-20 21:00:00	3
BBB	08/22/B	2022-08-22 21:00:00	1
CCC	08/22/C	2022-08-22 21:00:00	1
CCC	08/21/C	2022-08-21 21:00:00	2

How can I achieve this?

Are you really using Postgres 9.3 as implied by the fiddle? Please always declare your version of Postgres. — Erwin Brandstetter, Aug 22 '22 at 00:38

Erwin Brandstetter · Accepted Answer · 2022-08-22T00:51:08.093

Throw in GROUP BY file, path:

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;

db<>fiddle here

Assuming you want to work with max(created), i.e. the latest timestamp per group.

Window function with same result in subgroup

1 Answers1