Home > Software engineering >  Window function with same result in subgroup
Window function with same result in subgroup

Time:08-24

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?

CodePudding user response:

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.

Related:

  • Related