How to join only one row in joined table with postgres?
I've done something similar for a chat system, where room holds the metadata and list contains the messages. I ended up using the Postgresql LATERAL JOIN which worked like a charm.
SELECT MR.id AS room_id, MR.created_at AS room_created,
lastmess.content as lastmessage_content, lastmess.datetime as lastmessage_when
FROM message.room MR
LEFT JOIN LATERAL (
SELECT content, datetime
FROM message.list
WHERE room_id = MR.id
ORDER BY datetime DESC
LIMIT 1) lastmess ON true
ORDER BY lastmessage_when DESC NULLS LAST, MR.created_at DESC
For more info see https://heap.io/blog/engineering/postgresqls-powerful-new-join-type-lateral
select distinct on (author.id)
book.id, author.id, author.name, book.title as last_book
from
author
inner join
book on book.author_id = author.id
order by author.id, book.id desc
Check distinct on
SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.
With distinct on it is necessary to include the "distinct" columns in the order by
. If that is not the order you want then you need to wrap the query and reorder
select
*
from (
select distinct on (author.id)
book.id, author.id, author.name, book.title as last_book
from
author
inner join
book on book.author_id = author.id
order by author.id, book.id desc
) authors_with_first_book
order by authors_with_first_book.name
Another solution is to use a window function as in Lennart's answer. And another very generic one is this
select
book.id, author.id, author.name, book.title as last_book
from
book
inner join
(
select author.id as author_id, max(book.id) as book_id
from
author
inner join
book on author.id = book.author_id
group by author.id
) s
on s.book_id = book.id
inner join
author on book.author_id = author.id