- Explore
- Collaborate
- Visualize
- Connect
- Pricing
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;
From MySQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and MySQL
From MySQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and MySQL