By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE
INSERT 0 16
| row_number | name | thing_id | c_id | status | status_1_date |
|---|---|---|---|---|---|
| 1 | protocol://full/path/to/thing/thing_1.file1 | thing_1 | c_id | status_1 | 2023-09-29 09:00:01 |
| 2 | protocol://full/path/to/thing/thing_1.file2 | thing_1 | c_id | status_1 | 2023-09-29 09:00:02 |
| 3 | protocol://full/path/to/thing/thing_2.file5 | thing_2 | c_id | status_1 | 2023-09-29 09:00:02.5 |
| 4 | protocol://something else | thing_1 | c_id | status_1 | 2023-09-29 09:00:02.8 |
| 5 | protocol://full/path/to/thing/thing_2.file1 | thing_2 | c_id | status_1 | 2023-09-29 09:00:03 |
| 6 | protocol://full/path/to/thing/thing_2.file2 | thing_2 | c_id | status_1 | 2023-09-29 09:00:04 |
| 7 | protocol://full/path/to/thing/thing_2.file3 | thing_2 | c_id | status_1 | 2023-09-29 09:00:05 |
| 8 | protocol://full/path/to/thing/thing_2.file4 | thing_2 | different | status_1 | 2023-09-29 09:00:05.5 |
| 9 | protocol://full/path/to/thing/thing_3.file1 | thing_3 | c_id | status_1 | 2023-09-29 09:00:06 |
| 10 | protocol://full/path/to/thing/thing_4.file1 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.1 |
| 11 | protocol://full/path/to/thing/thing_3.file2 | thing_3 | c_id | status_1 | 2023-09-29 09:00:06.2 |
| 12 | protocol://full/path/to/thing/thing_4.file2 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.3 |
| 13 | protocol://full/path/to/thing/thing_5.file1 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.4 |
| 14 | protocol://full/path/to/thing/thing_5.file2 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.5 |
| 15 | protocol://full/path/to/thing/thing_5.file3 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.6 |
| 16 | protocol://full/path/to/thing/thing_6.file1 | thing_6 | c_id | status_1 | 2023-09-29 09:00:06.7 |
SELECT 16
| thing_rank | name | thing_id | c_id | status | status_1_date |
|---|---|---|---|---|---|
| 1 | protocol://full/path/to/thing/thing_1.file1 | thing_1 | c_id | status_1 | 2023-09-29 09:00:01 |
| 1 | protocol://full/path/to/thing/thing_1.file2 | thing_1 | c_id | status_1 | 2023-09-29 09:00:02 |
| 3 | protocol://full/path/to/thing/thing_2.file5 | thing_2 | c_id | status_1 | 2023-09-29 09:00:02.5 |
| 3 | protocol://full/path/to/thing/thing_2.file1 | thing_2 | c_id | status_1 | 2023-09-29 09:00:03 |
| 3 | protocol://full/path/to/thing/thing_2.file2 | thing_2 | c_id | status_1 | 2023-09-29 09:00:04 |
| 3 | protocol://full/path/to/thing/thing_2.file3 | thing_2 | c_id | status_1 | 2023-09-29 09:00:05 |
| 7 | protocol://full/path/to/thing/thing_3.file1 | thing_3 | c_id | status_1 | 2023-09-29 09:00:06 |
| 9 | protocol://full/path/to/thing/thing_4.file1 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.1 |
| 7 | protocol://full/path/to/thing/thing_3.file2 | thing_3 | c_id | status_1 | 2023-09-29 09:00:06.2 |
| 9 | protocol://full/path/to/thing/thing_4.file2 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.3 |
| 11 | protocol://full/path/to/thing/thing_5.file1 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.4 |
| 11 | protocol://full/path/to/thing/thing_5.file2 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.5 |
| 11 | protocol://full/path/to/thing/thing_5.file3 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.6 |
| 14 | protocol://full/path/to/thing/thing_6.file1 | thing_6 | c_id | status_1 | 2023-09-29 09:00:06.7 |
SELECT 14
| thing_rank | name | thing_id | c_id | status | status_1_date |
|---|---|---|---|---|---|
| 1 | protocol://full/path/to/thing/thing_1.file1 | thing_1 | c_id | status_1 | 2023-09-29 09:00:01 |
| 1 | protocol://full/path/to/thing/thing_1.file2 | thing_1 | c_id | status_1 | 2023-09-29 09:00:02 |
| 2 | protocol://full/path/to/thing/thing_2.file5 | thing_2 | c_id | status_1 | 2023-09-29 09:00:02.5 |
| 2 | protocol://full/path/to/thing/thing_2.file1 | thing_2 | c_id | status_1 | 2023-09-29 09:00:03 |
| 2 | protocol://full/path/to/thing/thing_2.file2 | thing_2 | c_id | status_1 | 2023-09-29 09:00:04 |
| 2 | protocol://full/path/to/thing/thing_2.file3 | thing_2 | c_id | status_1 | 2023-09-29 09:00:05 |
| 3 | protocol://full/path/to/thing/thing_3.file1 | thing_3 | c_id | status_1 | 2023-09-29 09:00:06 |
| 4 | protocol://full/path/to/thing/thing_4.file1 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.1 |
| 3 | protocol://full/path/to/thing/thing_3.file2 | thing_3 | c_id | status_1 | 2023-09-29 09:00:06.2 |
| 4 | protocol://full/path/to/thing/thing_4.file2 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.3 |
| 5 | protocol://full/path/to/thing/thing_5.file1 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.4 |
| 5 | protocol://full/path/to/thing/thing_5.file2 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.5 |
| 5 | protocol://full/path/to/thing/thing_5.file3 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.6 |
| 6 | protocol://full/path/to/thing/thing_6.file1 | thing_6 | c_id | status_1 | 2023-09-29 09:00:06.7 |
SELECT 14
| name | thing_id | c_id | status | status_1_date |
|---|---|---|---|---|
| protocol://full/path/to/thing/thing_1.file1 | thing_1 | c_id | CHANGED | 2023-09-29 09:00:01 |
| protocol://full/path/to/thing/thing_1.file2 | thing_1 | c_id | CHANGED | 2023-09-29 09:00:02 |
| protocol://full/path/to/thing/thing_2.file5 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:02.5 |
| protocol://full/path/to/thing/thing_2.file1 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:03 |
| protocol://full/path/to/thing/thing_2.file2 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:04 |
| protocol://full/path/to/thing/thing_2.file3 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:05 |
| protocol://full/path/to/thing/thing_3.file1 | thing_3 | c_id | CHANGED | 2023-09-29 09:00:06 |
| protocol://full/path/to/thing/thing_3.file2 | thing_3 | c_id | CHANGED | 2023-09-29 09:00:06.2 |
UPDATE 8
| row_number | name | thing_id | c_id | status | status_1_date |
|---|---|---|---|---|---|
| 1 | protocol://full/path/to/thing/thing_1.file1 | thing_1 | c_id | CHANGED | 2023-09-29 09:00:01 |
| 2 | protocol://full/path/to/thing/thing_1.file2 | thing_1 | c_id | CHANGED | 2023-09-29 09:00:02 |
| 3 | protocol://full/path/to/thing/thing_2.file5 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:02.5 |
| 4 | protocol://something else | thing_1 | c_id | status_1 | 2023-09-29 09:00:02.8 |
| 5 | protocol://full/path/to/thing/thing_2.file1 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:03 |
| 6 | protocol://full/path/to/thing/thing_2.file2 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:04 |
| 7 | protocol://full/path/to/thing/thing_2.file3 | thing_2 | c_id | CHANGED | 2023-09-29 09:00:05 |
| 8 | protocol://full/path/to/thing/thing_2.file4 | thing_2 | different | status_1 | 2023-09-29 09:00:05.5 |
| 9 | protocol://full/path/to/thing/thing_3.file1 | thing_3 | c_id | CHANGED | 2023-09-29 09:00:06 |
| 10 | protocol://full/path/to/thing/thing_4.file1 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.1 |
| 11 | protocol://full/path/to/thing/thing_3.file2 | thing_3 | c_id | CHANGED | 2023-09-29 09:00:06.2 |
| 12 | protocol://full/path/to/thing/thing_4.file2 | thing_4 | c_id | status_1 | 2023-09-29 09:00:06.3 |
| 13 | protocol://full/path/to/thing/thing_5.file1 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.4 |
| 14 | protocol://full/path/to/thing/thing_5.file2 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.5 |
| 15 | protocol://full/path/to/thing/thing_5.file3 | thing_5 | c_id | status_1 | 2023-09-29 09:00:06.6 |
| 16 | protocol://full/path/to/thing/thing_6.file1 | thing_6 | c_id | status_1 | 2023-09-29 09:00:06.7 |
SELECT 16