How to Get the First Row per Group in MySQL

Starting with version 8.0.2, MySQL now offers a way to easily number rows. For example, if we want to list the number the films in the Sakila Sample Database, grouped by rating and ordered by release year:

SELECT *,
  row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
FROM film;

Now if you only want to get the first row for each rating, you can use a common table expression:

WITH _films AS (
  SELECT *,
    row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
  FROM film;
)
SELECT *
FROM _films
WHERE row_num = 1;