Return unique grouped rows with the latest timestamp

Question

At the moment I'm struggling with a problem that looks very easy.

Tablecontent:

Primay Keys: Timestamp, COL_A,COL_B ,COL_C,COL_D        

+------------------+-------+-------+-------+-------+--------+--------+
|    Timestamp     | COL_A | COL_B | COL_C | COL_D | Data_A | Data_B |
+------------------+-------+-------+-------+-------+--------+--------+
| 31.07.2019 15:12 |     - | -     |     - |     - |      1 |      2 |
| 31.07.2019 15:32 |     1 | 1     |   100 |     1 |   5000 |     20 |
| 10.08.2019 09:33 |     - | -     |     - |     - |   1000 |      7 |
| 31.07.2019 15:38 |     1 | 1     |   100 |     1 |     33 |      5 |
| 06.08.2019 08:53 |     - | -     |     - |     - |      0 |      7 |
| 06.08.2019 09:08 |     - | -     |     - |     - |      0 |      7 |
| 06.08.2019 16:06 |     3 | 3     |     3 |     3 |      0 |     23 |
| 07.08.2019 10:43 |     - | -     |     - |     - |      0 |     42 |
| 07.08.2019 13:10 |     - | -     |     - |     - |      0 |     24 |
| 08.08.2019 07:19 |    11 | 111   |   111 |    12 |      0 |      2 |
| 08.08.2019 10:54 |  2334 | 65464 |   565 |    76 |   1000 |     19 |
| 08.08.2019 11:15 |   232 | 343   |   343 |    43 |      0 |      2 |
| 08.08.2019 11:30 |  2323 | rtttt |  3434 |    34 |      0 |      2 |
| 10.08.2019 14:47 |     - | -     |     - |     - |    123 |     23 |
+------------------+-------+-------+-------+-------+--------+--------+

Needed query output:

+------------------+-------+-------+-------+-------+--------+--------+
|    Timestamp     | COL_A | COL_B | COL_C | COL_D | Data_A | Data_B |
+------------------+-------+-------+-------+-------+--------+--------+
| 31.07.2019 15:38 |     1 | 1     |   100 |     1 |     33 |      5 |
| 06.08.2019 16:06 |     3 | 3     |     3 |     3 |      0 |     23 |
| 08.08.2019 07:19 |    11 | 111   |   111 |    12 |      0 |      2 |
| 08.08.2019 10:54 |  2334 | 65464 |   565 |    76 |   1000 |     19 |
| 08.08.2019 11:15 |   232 | 343   |   343 |    43 |      0 |      2 |
| 08.08.2019 11:30 |  2323 | rtttt |  3434 |    34 |      0 |      2 |
| 10.08.2019 14:47 |     - | -     |     - |     - |    123 |     23 |
+------------------+-------+-------+-------+-------+--------+--------+

As you can see, I'm trying to get single rows for my primary keys, using the latest timestamp, which is also a primary key.

Currently, I tried a query like:

SELECT Timestamp, COL_A, COL_B, COL_C, COL_D, Data_A, Data_B From Table XY op


WHERE Timestamp = (
    SELECT MAX(Timestamp) FROM XY as tsRow
    WHERE op.COL_A = tsRow.COL_A 
    AND op.COL_B = tsRow.COL_B
    AND op.COL_C = tsRow.COL_C 
    AND op.COL_D  = tsRow."COL_D
);

which gives me result that looks fine at first glance.

Is there a better or more safe way to get my preferred result?

score 0 · Answer 1 · answered Aug 12 '19 at 15:32

demo:db<>fiddle

You can use the DISTINCT ON clause, which gives you the first record of an ordered group. Here your group is your (A, B, C, D). This is ordered by the Timestamp column, in descending order, to get the most recent record to be the first.

SELECT DISTINCT ON ("COL_A", "COL_B", "COL_C", "COL_D")
    *
FROM
    mytable
ORDER BY "COL_A", "COL_B", "COL_C", "COL_D", "Timestamp" DESC

If you want to get your expected order, you need a second ORDER BY after this operation:

SELECT
    *
FROM (
    SELECT DISTINCT ON ("COL_A", "COL_B", "COL_C", "COL_D")
        *
    FROM
        mytable
    ORDER BY "COL_A", "COL_B", "COL_C", "COL_D", "Timestamp" DESC
) s
ORDER BY "Timestamp"

Note: If you have the Timestamp column as part of the PK, are you sure, you really need the four other columns as PK as well? It seems, that the TS column is already unique.

Dear @NicolasWeis I would appreciate some feedback if this helped or not :) Please consider to accept the answer if it helped to solve your problem. So I would know that I didn't make a mistake and all other know that they do not need to invest any more time into it. The second step, upvoting, generates some internal credits to each replier. So it honors the time and work they invested into your problem and motivate them to do this furthermore. So please check your questions for good answers and do not hesitate to upvote and accept them. :) — S-Man, Aug 16 '19 at 06:51

Return unique grouped rows with the latest timestamp

1 Answers1