Postgresで結合されたテーブルの1行だけを結合する方法は？

質問

以下のスキーマがあります。

CREATE TABLE author (
    id   integer
  , name varchar(255)
);
CREATE TABLE book (
    id        integer
  , author_id integer
  , title     varchar(255)
  , rating    integer
);

そして、それぞれの著者の最後の本が欲しい。

SELECT book.id, author.id, author.name, book.title as last_book
FROM author
JOIN book book ON book.author_id = author.id

GROUP BY author.id
ORDER BY book.id ASC

どうやらmysqlでできるようです。 MySQL で 2 つのテーブルを結合し、2 番目のテーブルから 1 行だけを返す。 .

しかし、postgresはこのエラーを出します。

ERROR: 列 "book.id" は GROUP BY 節に含まれるか、または集約関数で使用される必要があります。 を集約関数で使用しなければなりません。SELECT book.id, author.id, author.name, book.title as last_book FROM author JOIN book book ON book.author_id = author.id GROUP BY author.id ORDER BY book.id ASC

それは :

GROUP BY が存在する場合、SELECT リスト式は有効ではありません。 式でグループ化されていない列を参照することは、集約関数内を除き、無効です。 関数を除いて、グループ化されていない列を参照することはできません。 というのも、グループ化されていない列に対して

どのようにpostgresに指定することができますか: "で並べられたとき、最後の行だけを私に与えてください。 joined_table.id で並べた場合、最後の行だけをください。

編集してください。 このデータで

INSERT INTO author (id, name) VALUES
  (1, 'Bob')
, (2, 'David')
, (3, 'John');

INSERT INTO book (id, author_id, title, rating) VALUES
  (1, 1, '1st book from bob', 5)
, (2, 1, '2nd book from bob', 6)
, (3, 1, '3rd book from bob', 7)
, (4, 2, '1st book from David', 6)
, (5, 2, '2nd book from David', 6);

見てみようかな。

book_id author_id name    last_book
3       1         "Bob"   "3rd book from bob"
5       2         "David" "2nd book from David"

どのように解決するのですか？

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

チェック distinct on

SELECT DISTINCT ON ( expression [, ...] ) は、与えられた式が等しいと評価される各行の集合の最初の行だけを保持します。DISTINCT ON式はORDER BYと同じ規則で解釈されます（上記参照）。ORDER BYが目的の行が最初に現れることを確実にするために使用されない限り、各集合のquot;最初の行"は予測不可能であることに注意してください。

distinctをオンにすると、"distinct"カラムを order by . もしこの順序が正しくない場合は、クエリをラップして順序を変更する必要があります。

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

もう一つの解決策は、Lennartの答えのようにウィンドウ関数を使うことです。そして、もう一つの非常に一般的なものはこれです

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