The problem is to write the "ADD-VALUE" request defined below.
For each "cat" value from the table below, keep only 3 records. Figure this table:
id | cat | value | updatedAt |
---|---|---|---|
1 | cat1 | v1 | 06/01/2021 00:00:01 |
2 | cat1 | v2 | 06/01/2021 00:00:02 |
3 | cat1 | v3 | 06/01/2021 00:00:03 (pointer cat1 is here) |
4 | cat2 | v1 | 06/01/2021 00:01:01 |
5 | cat2 | v2 | 06/01/2021 00:01:02 (pointer cat2 is here) |
INSERT case: Calling "ADD-VALUE(cat=cat2, value=v3)" will produce the result in bold:
id | cat | value | updatedAt |
---|---|---|---|
1 | cat1 | v1 | 06/01/2021 00:00:01 |
2 | cat1 | v2 | 06/01/2021 00:00:02 |
3 | cat1 | v3 | 06/01/2021 00:00:03 (pointer cat1 is here) |
4 | cat2 | v1 | 06/01/2021 00:01:01 |
5 | cat2 | v2 | 06/01/2021 00:01:02 |
6 | cat2 | v3 | 06/01/2021 00:01:02 (pointer cat2 is now here) |
UPDATE case: Calling "ADD-VALUE(cat=cat1, value=v4)" will produce the result in bold:
id | cat | value | updatedAt |
---|---|---|---|
1 | cat1 | v4 | 07/01/2021 00:00:04 (pointer cat1 is now here) |
2 | cat1 | v2 | 06/01/2021 00:00:02 |
3 | cat1 | v3 | 06/01/2021 00:00:03 |
4 | cat2 | v1 | 06/01/2021 00:01:01 |
5 | cat2 | v2 | 06/01/2021 00:01:02 (pointer cat2 is here) |
Any advice is welcome. UPDATE or INSERT in one request is maybe impossible? I think about using row-num to have the count per category.
insert ... on conflict do update
syntax? Though having 3 records rather than 1 makes this a bit trickier.CREATE TABLE
statement with actual data types and constraints. (Even if it's just a work in progress so far.) And explain your requirements some more. Can there beDELETE
orUPDATE
?