How to join only one row in joined table with postgres?

select distinct on (author.id)    book.id, author.id, author.name, book.title as last_bookfrom    author    inner join    book on book.author_id = author.idorder 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_bookorder 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_bookfrom    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

This may look archaic and overly simple, but it does not depend on window functions, CTE's and aggregating subqueries. In most cases it is also the fastest.

SELECT bk.id, au.id, au.name, bk.title as last_bookFROM author auJOIN book bk ON bk.author_id = au.idWHERE NOT EXISTS (    SELECT *    FROM book nx    WHERE nx.author_id = bk.author_id    AND nx.book_id > bk.book_id    )ORDER BY book.id ASC    ;

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_whenFROM 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 trueORDER 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