sqlpostgresqlsubqueryleft-joinpostgres-10

How to join latest 2 rows into a view using postgres 10


I want to create a view where I can join the two latest statuses with their timestamps and comments to the view. I know I can use min and max for the timestamps, but how can I set their comments correctly (see comment in code)? What is the most efficient way to do this? (apart from comment I have around 10 columns more I need to join for the current and previous status).

I'm using postgres v10

SELECT
w.workplace_id,
max(current_and_previous_statuses.timestamp) AS current_status_timestamp,
min(current_and_previous_statuses.timestamp) AS previous_status_timestamp
-- current_status.comment
-- previous_status.comment
FROM workplace w
    LEFT JOIN (
        SELECT
        ws.workplace_status_id,
        ws.timestamp,
        ws.fk_workplace_id,
        ws.comment
        FROM workplace_status ws
        ORDER BY ws.timestamp DESC
        LIMIT 2
    ) current_and_previous_statuses ON current_and_previous_statuses.fk_workplace_id = w.workplace_id
GROUP BY w.workplace_id

Solution

  • Thanks for the answers above!

    I actually solved it using the postgres lag function.

    With the lag function I was able to create a sub query, where I first "lag" the previous columns to the workplace status. So each row contains the info of the previous status. Afterwards it was a simple left join.