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: