PostgreSQL – How to Join Most Recent Row

join;postgresql

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?

Best Answer

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.

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

SQL Server Join – How to Join by Most Recent Preceding Date

select *
from dbo.Sales s
  cross apply (
  select top (1) *
  from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date
  order by ph.Date desc
) ca;

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

SQL Server Join – How to Join by Most Recent Preceding Date

Related Question