Instruction
- Write a query to return the title of the second shortest film based on its duration/length.
- A movie's duration can be found using the length column.
- If there are ties, return just one of them.
Table: film
col_name | col_type
----------------------+--------------------------
film_id | integer
title | text
description | text
release_year | integer
language_id | smallint
original_language_id | smallint
rental_duration | smallint
rental_rate | numeric
length | smallint
replacement_cost | numeric
rating | text
Sample results
title
--------------
SECOND SHORTEST
Expected results
Solution 1: postgres
SELECT title
FROM film
ORDER BY length
LIMIT 1
OFFSET 1;
Explanation
This query is asking the database to retrieve the title of a movie from the film table. The results are ordered by the length of the movie in ascending order. The LIMIT 1 clause means that only one movie title will be returned. The OFFSET 1 clause means that the first movie in the result set will be skipped, and the second movie title will be returned.
Solution 2: postgres
-- WINDOW FUNCTION BASED SOLUTION
WITH film_length_ranking AS (
SELECT title, ROW_NUMBER() OVER (ORDER BY length) ranking
FROM film
)
SELECT title
FROM film_length_ranking
WHERE ranking = 2;
Explanation
This query is using a window function to rank the films by length and then selecting the title of the film that has a ranking of 2.
First, the query creates a Common Table Expression (CTE) called "film_length_ranking". This CTE selects the film title and assigns a ranking to each film based on its length. The "ROW_NUMBER()" function is used with the "OVER (ORDER BY length)" clause to assign a ranking to each film based on its length, with the shortest film being ranked 1.
Next, the query selects the title of the film that has a ranking of 2. The "WHERE" clause is used to filter the results to only show the film with a ranking of 2.
Overall, this query is selecting the title of the film that is ranked second in terms of length.
Solution 3: postgres
-- FOR THOSE FOLLOWING THE COURSE
WITH shortest_2 AS (
SELECT film_id, length
FROM film
ORDER BY length
LIMIT 2
)
SELECT title
FROM film
WHERE film_id IN (
SELECT film_id
FROM shortest_2
ORDER BY length DESC
LIMIT 1
);
Explanation
This query is selecting the title of the film with the second shortest length. It first creates a temporary table called "shortest_2" that selects the film_id and length from the "film" table and orders them by length in ascending order. It then selects the top 2 rows from this table by using the "LIMIT 2" clause.
The outer query then selects the title from the "film" table where the film_id is in the temporary table "shortest_2", but only selects the film with the longest length of the two shortest films by ordering the temporary table by length in descending order and selecting the top row with "LIMIT 1".
Overall, this query is finding the title of the film that is second shortest in length, but is longer than the shortest film in the "film" table.