Question 189951 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Get Most Recent Date based on Group By

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

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

How do I get the following output below on PostgreSQL:
enter image description here

Tags:

2

Answers


  1. 0

    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()
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search