Order by author name when a book may have more than one author

Question

I've got a book catalogue db. Each book can have one or more authors. Example:

book #1 by John Red
book #2 by Fred Green and Steve Blue
book #3 by Ben Brown

ARTICLES table:

id	title
1	Book1
2	Book2
3	Book3

AUTHORS table:

id	surname	name
1	Red	John
2	Green	Fred
3	Blue	Steve
4	Brown	Ben

AUTHORS_ARTICLES table:

id_author	id_article
1	1
2	2
3	2
4	3

I need to retrieve books' details, sorted in alphabetical order by author.

I am using the following query:

SELECT DISTINCT a.*
FROM articles a
LEFT OUTER JOIN authors_articles aa ON a.id = aa.id_article
LEFT OUTER JOIN authors au ON aa.id_author = au.id
ORDER BY au.surname ASC, au.name ASC

The problem is that books do not always get sorted alphabetically by authors' surname/name: when there are two or more authors for a book, the first author appearing in the link table (authors_articles) will be the one returned from the query, and that is not necessarily the first in alphabetical order. Still, it will be used by the ORDER BY clause at the end of query, resulting in erroneous order of records.

The result for my current query is:

id	title
3	Book3
2	Book2
1	Book1

Notice that the book by Brown comes first, even when Book2 is authored by Green AND Blue.

I would need them to be listed as Book2, Book3 and Book1, instead.

Any help will be appreciated.

Answer 1

You need to find the first author per book before you can perform an order by. A simple row_number query could be used for that:

with cte as (
  select
    articles.*,
    authors.surname,
    authors.name,
    row_number() over (
      partition by articles.id
      order by authors.surname, authors.name
    ) as rn
  from articles
  left join authors_articles on articles.id =  authors_articles.id_article
  left join authors on authors_articles.id_author = authors.id
)
select *
from cte
where rn = 1
order by surname, name