Get most recent date based on Group By in PostgreSQL [closed]

Question

I have a movie table with over 100 different movies, shown below on PostgreSQL:

I want the name of each most-recent released movie, according to EACH GENRE.

How do I get the following output below in PostgreSQL?

Answer 1

You can use a window function such as DENSE_RANK() and filter by returning value equals to 1 like in the following one

SELECT Genre, Movie_Name 
  FROM
  (
   SELECT t.*, DENSE_RANK() OVER (PARTITION BY Genre ORDER BY Released_Date DESC) AS dr
    FROM t
  ) tt
 WHERE dr = 1

where

• PARTITION BY means GROUP BY
• DESCendingly ORDERing BY means to get the latest ones
• DENSE_RANK() function keeps ties of Released_Dates(equal date values per each group) unlike to ROW_NUMBER()

Answer 2

Use DISTINCT ON to get distinct genres. The ORDER BY will give you the most recent one for each genre.

SELECT DISTINCT ON (Genre) Movie_Name, Genre, Released_Date 
FROM movie_table m
ORDER BY Genre, Released_Date DESC