Merge rows in PostgreSQL and replace values with latest when not null, but join on other column

user_3465416 0
Viewed 265

My question is 90% the same as the one below, but with a twist that makes it a bit more complicated: Merge rows postgres and replace values with latest when not null

The difference is that in the original question the max(case when slug is not null then id end) slugid was also what should be joined on, but for me the join should happen based on the value of another column.

Example documents table (contracts to documents is a 1:many relationship):

id contract_id timestamp a b c
UUID-C UUID-A 2021-08-18 10:00 X Y Z
UUID-D UUID-A 2021-08-18 11:00 null F null
UUID-E UUID-B 2021-08-18 11:00 F null null
UUID-F UUID-B 2021-08-18 12:00 null G null
UUID-G UUID-B 2021-08-18 13:00 X null H

Expected result:

contract_id a b c
UUID-A X F Z
UUID-B X G H

The documents column that should be used for determining the most recent (and therefore, most important) row is timestamp, but the documents.id should be used for merging the results instead of the timestamp in order to prevent unexpected behaviour.

An example of how the most recent timestamps are selected for each a, b, c value is this:

SELECT
    contract_id,
    max(case when a is not null then timestamp end) a_timestamp,
    max(case when b is not null then timestamp end) b_timestamp,
    max(case when c is not null then timestamp end) c_timestamp,
    FROM documents
    GROUP BY contract_id;

but then the join should happen based on id, not on timestamp.

How do I do this?

postgresql
2 Answers

The solution turned out to be much simpler than expected, no joins are required at all when solving it like this:

SELECT c.id,
    (SELECT a FROM documents d WHERE d.a IS NOT NULL AND d.contract_id = c.id ORDER BY timestamp DESC LIMIT 1) as a,
    (SELECT b FROM documents d WHERE d.b IS NOT NULL AND d.contract_id = c.id ORDER BY timestamp DESC LIMIT 1) as b,
    (SELECT c FROM documents d WHERE d.c IS NOT NULL AND d.contract_id = c.id ORDER BY timestamp DESC LIMIT 1) as c,
FROM contracts c;

In reality, there are not 3 (abc) but 10 (abcdefghij) fields. If someone knows a way to write this query more elegantly without repeating the same line 10 times, then please tell me in a comment :-).

user_34654160

After a few false starts I came up with this.

CREATE FUNCTION coalesce2(anyelement,anyelement) RETURNS anyelement
      LANGUAGE sql AS 'select coalesce($1,$2)' ;
CREATE AGGREGATE firstval_agg(anyelement)
      (sfunc=coalesce2,stype=anyelement);

SELECT DISTINCT ON ( contract_id ) 
  contract_id 
  ,firstval_agg(a) OVER w AS timestamp_a
  ,firstval_agg(b) OVER w AS timestamp_b
  ,firstval_agg(c) OVER w AS timestamp_c
FROM documents 
WINDOW w AS (partition by contract_id order by timestamp DESC)
ORDER BY contract_id ,timestamp;

The ,firstval_agg(a) OVER w AS timestamp_a part and WINDOW w AS... part collect the desired values on the first row of each group

the DISTINCT ON ( contract_id ) and ORDER BY... causes it to return only the first row of each group.

result:

 contract_id | timestamp_a | timestamp_b | timestamp_c 
-------------+-------------+-------------+-------------
 UUID-A      | X           | F           | Z
 UUID-B      | X           | G           | H
user_4719300
Related