Christian Persson
09/29/2021, 10:17 AMWITH merged_stream AS (
SELECT id, 0 updated_at, expression1, expression2, ... FROM old_table
UNION ALL
SELECT id, 1 updated_at, expression1, expression2, ... FROM new_table
)
SELECT id, FIRST(expression1), FIRST(expression2),... FROM
(
SELECT id, expression1, expression2, ...
FROM merged_stream
ORDER BY id, updated_at
) tmp
GROUP BY id
Wouldn't it be nice if you instead could do something like this instead?
WITH merged_stream AS (
SELECT id, 0 updated_at, expression1, expression2, ... FROM old_table
UNION ALL
SELECT id, 1 updated_at, expression1, expression2, ... FROM new_table
)
SELECT * FROM
merged_stream
DEDUP BY id ON DUPLICATE KEY (TAKE LAST RECORD | TAKE FIRST RECORD | COALESCE | PANIC ) ORDERED BY updated_at
Do you know if this kind of construction exist somewhere already? Or do you know a more elegant way to express it with known SQL (instead of the GROUP BY example).DISTINCT ON
, that is close enough to what I'am looking for I think.
https://www.geekytidbits.com/postgres-distinct-on/Deividas J
09/29/2021, 11:36 AMdistinct on
does the jobChristian Persson
09/29/2021, 11:36 AMWITH merged_stream AS (
SELECT id, 0 updated_at, expression1, expression2, ... FROM old_table
UNION ALL
SELECT id, 1 updated_at, expression1, expression2, ... FROM new_table
)
SELECT DISTINCT ON(id) * FROM
merged_stream
ORDER BY id, updated_at DESC
would be the postgres equivalent to the TAKE LAST RECORD
and TAKE FIRST RECORD
modes. Nice.Marco Fontana
09/29/2021, 1:30 PMRANK
function