Created
November 24, 2022 12:44
-
-
Save rafaeltedesco/83aeb323fd0dcddb162f1d6ca54ea8b5 to your computer and use it in GitHub Desktop.
'Over partition' example using 'row_number' to present a cumulative view of movies that each actor have been participated
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USE sakila; | |
| SELECT | |
| CONCAT(tbl_actor.last_name, | |
| ' ', | |
| tbl_actor.first_name) AS actor_name, | |
| tbl_film.title AS movie, | |
| ROW_NUMBER() OVER(PARTITION BY CONCAT(tbl_actor.last_name, | |
| ' ', | |
| tbl_actor.first_name)) AS total_movies | |
| FROM | |
| film_actor AS tbl_f_actor | |
| INNER JOIN | |
| actor AS tbl_actor ON tbl_f_actor.actor_id = tbl_actor.actor_id | |
| INNER JOIN | |
| film AS tbl_film ON tbl_film.film_id = tbl_f_actor.film_id | |
| WHERE (tbl_actor.first_name = 'KIRSTEN' AND tbl_actor.last_name = 'AKROYD') | |
| OR (tbl_actor.first_name = 'ED' AND tbl_actor.last_name = 'CHASE') | |
| ORDER BY actor_name, total_movies; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment