Postgres join table select only row with latest timestamp

Anton Hoerl 0
Viewed 42

I cannot join tables setup, p and sps. I only want to join the row from sps with the newest timestamp in the created_at column.

I tried this answer: https://stackoverflow.com/a/45018194/6535278 Without any success like so:

SELECT *
FROM setup s
JOIN (SELECT p_id AS p_id_1,
        p_setup_id AS p_setup_id_1
    FROM part
    ) p
ON s.s_id = p.p_setup_id_1 
JOIN (SELECT DISTINCT ON (s_p_s_id) s_p_s_id AS s_p_s_id_1,
        part_id AS part_id_1,
        s_setup_id AS s_setup_id_1,
        created_at AS created_at_1
    FROM setup_part_setting
    ORDER BY s_p_s_id, created_at
    ) sps
ON s.s_id = sps.s_setup_id_1
AND p.p_id_1 = sps.part_id_1
WHERE s.s_id IS NOT NULL
ORDER BY s.created_at;

My code:

SELECT *
FROM setup s
JOIN (SELECT p_id AS p_id_1,
    p_setup_id AS p_setup_id_1
    FROM part) p
ON s.s_id = p.p_setup_id_1 
JOIN (SELECT s_p_s_id AS s_p_s_id_1,
    part_id AS part_id_1,
    s_setup_id AS s_setup_id_1,
    created_at AS created_at_1
    FROM setup_part_setting) sps
ON s.s_id = sps.s_setup_id_1
AND p.p_id_1 = sps.part_id_1
WHERE s.s_id IS NOT NULL
ORDER BY s.created_at;
join postgresql
1 Answers

Feel free to sort subselection before to JOIN. Or even limit it:

SELECT *
FROM setup s
JOIN (
    SELECT p_id AS p_id_1, p_setup_id AS p_setup_id_1
    FROM part
) p ON s.s_id = p.p_setup_id_1 
JOIN (
    SELECT 
        s_p_s_id AS s_p_s_id_1, 
        part_id AS part_id_1,
        s_setup_id AS s_setup_id_1,
        created_at AS created_at_1
    FROM setup_part_setting
    ORDER BY created_at DESC
    LIMIT 1
) sps ON 
    s.s_id = sps.s_setup_id_1
    AND p.p_id_1 = sps.part_id_1
WHERE s.s_id IS NOT NULL
ORDER BY s.created_at;

You can simplify your query a lot if you will use JOIN LEFT instead of joined selects. Just compare your query with the same:

SELECT s.*, p.p_id, p.p_setup_id, sps.s_p_s_id, sps.part_id, sps.s_setup_id, sps.created_at 
FROM setup s
LEFT JOIN part p ON s.s_id = p.p_setup_id
LEFT JOIN setup_part_setting sps ON s.s_id = sps.s_setup_id AND p.p_id = sps.part_id
WHERE s.s_id IS NOT NULL
ORDER BY sps.created_at DESC, s.created_at ASC;
Eugene Kaurov0
Related