Postgresql - join most recent row I have a query:

SELECT 
    Author.id,
    Author.name,
    Author.surname,
    Author.photoURL as photoURL,
    Movie.title as lastMovieTitle,
    Movie.publicationDate as lastMovieDate,
    Movie.languageID as lastMovieLanguage,
    Series.name as lastMovieSeriesName,
    CASE WHEN UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' THEN TRUE ELSE FALSE END as favourited,
    sum(CASE WHEN MovieRating.ratingUp = TRUE THEN 1 ELSE 0 END) as ratingUp,
    sum(CASE WHEN MovieRating.ratingDown = TRUE THEN 1 ELSE 0 END) as ratingDown,
    count(DISTINCT Movie.id) as movieCount 

FROM Author 

LEFT JOIN Movie ON Author.id = Movie.authorID 
LEFT JOIN Series ON Movie.seriesID = Series.id 
LEFT JOIN UserAuthorFavourited ON UserAuthorFavourited.authorID = Author.id AND UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN MovieRating ON Movie.id = MovieRating.movieID AND Movie.authorID = Author.id 

GROUP BY 
    Author.id,
    Movie.id,
    Series.id,
    UserAuthorFavourited.userID 

ORDER BY Author.surname ASC, Movie.publicationDate DESC


If author has more than one Movie it lists this Author duplicated rows instead of listing only once with most recent movie (not as many times as there are this authors movies) I tried to add Distinct ON Author.id but this not change anything.

How to refactor this query to return proper results?
You can use a derived table that picks the latest movie, then join to that:

SELECT 
    Author.id,
    Author.name,
    Author.surname,
    Author.photoURL as photoURL,
    Movie.title as lastMovieTitle,
    Movie.publicationDate as lastMovieDate,
    Movie.languageID as lastMovieLanguage,
    Series.name as lastMovieSeriesName,
    UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' as favourited,
    count(*) filter (where MovieRating.ratingUp) as ratingUp,
    count(*) filter (where MovieRating.ratingDown) as ratingDown,
    count(DISTINCT Movie.id) as movieCount 
FROM Author 
  LEFT JOIN (
      SELECT DISTINCT ON (authorid) *
      FROM movie 
      ORDER BY authorid, publicationDate DESC
    ) Movie ON Author.id = Movie.authorID 
  LEFT JOIN Series ON Movie.seriesID = Series.id 
  LEFT JOIN UserAuthorFavourited ON UserAuthorFavourited.authorID = Author.id AND UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' 
  LEFT JOIN MovieRating ON Movie.id = MovieRating.movieID AND Movie.authorID = Author.id 
GROUP BY 
    Author.id,
    Movie.id,
    Series.id,
    UserAuthorFavourited.userID 
ORDER BY Author.surname ASC, Movie.publicationDate DESC


I also simplified the expression for the favourited column and the conditional counting.