One row for each association id SQL
CREATE OR REPLACE VIEW next_episode_ids AS ( | |
SELECT id, ROW_NUMBER() OVER(PARTITION BY show_id ORDER BY airs_on) AS r | |
FROM episodes | |
WHERE airs_on >= NOW() | |
); | |
CREATE OR REPLACE VIEW next_episodes AS ( | |
SELECT * FROM episodes WHERE id IN ( | |
SELECT id FROM next_episode_ids WHERE r = 1 | |
) | |
); | |
SELECT * FROM shows | |
LEFT JOIN next_episodes ON next_episodes.show_id = shows.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment