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

Question

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

Answer 1

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.

Answer 2

You can use analytical function as follows:

SELECT
workplace_id,
max(timestamp) AS current_status_timestamp,
min(timestamp) AS previous_status_timestamp,
Max(case when rn = 1 the comment end) as current_comment,
Max(case when rn = 2 the comment end) as previous_comment, 
From
(Select w.workplace_id, ws.timestamp, ws.comment,
Row_number() over (partition by w.workplace_id order by ws.timestamp desc) as rn
 FROM workplace w
 LEFT JOIN workplace_status ws
   ON ws.fk_workplace_id = w.workplace_id) t
Where rn <= 2
GROUP BY workplace_id

Answer 3

The most efficient way to do this might be two lateral joins:

select w.*, ws1.timestamp, ws2.timestamp, ws1.comment, ws2.comment
from workplace w left join lateral
     (select ws.*
      from workplace_status ws
      where ws.fk_workplace_id = w.workplace_id
      order by ws.timestamp desc
      limit 1
     ) ws1
     on 1=1 left join lateral
     (select ws.*
      from workplace_status ws
      where ws.fk_workplace_id = w.workplace_id
      order by ws.timestamp desc
      limit 1 offset 1
     ) ws2
     on 1=1;

For performance, this needs an index on worksplace_status(workplace_id, timestamp).

A similar alternative -- and probably very similar performance -- is to use a single subquery:

select w.*, ws.*
from workplace w cross join lateral
     (select max(timestamp) filter (where seqnum = 1) as max_timestamp,
             max(timestamp) filter (where seqnum = 2) as min_timestamp,
             max(comment) filter (where seqnum = 1) as max_comment,
             max(comment) filter (where seqnum = 2) as min_comment                 
      from (select ws.*,
                   row_number() over (partition by ws.fk_workplace_id order by ws.timestamp desc) as seqnum
            from workplace_status ws
            where ws.fk_workplace_id = w.workplace_id
           )
      where seqnum <= 2
     ) ws;

In both cases, these avoid an aggregation on the larger dataset, which should be a win for performance.