1

I need to write an SQL query to identify the title of the film with the longest running time and I'm just wondering how I would do that? I've tried this but I'm not sure exactly what I need to do to fix the statement.

select f.film_title from film f order by f.film_len desc limit 1;

I thought the simplest approach would be to simply sort the movies by length and sort them in ascending order. Then only take the first result which would be the longest movie. However, this does not take into account films with the same length.

And this is the table I've created that I have to find the results from.

drop table film_director; drop table film_actor; drop table film; drop table studio; drop table actor; drop table director; CREATE TABLE studio( studio_ID NUMBER NOT NULL, studio_Name VARCHAR2(30), PRIMARY KEY(studio_ID)); CREATE TABLE film( film_ID NUMBER NOT NULL, studio_ID NUMBER NOT NULL, genre VARCHAR2(30), genre_ID NUMBER(1), film_Len NUMBER(3), film_Title VARCHAR2(30) NOT NULL, year_Released NUMBER NOT NULL, PRIMARY KEY(film_ID), FOREIGN KEY (studio_ID) REFERENCES studio); CREATE TABLE director( director_ID NUMBER NOT NULL, director_fname VARCHAR2(30), director_lname VARCHAR2(30), PRIMARY KEY(director_ID)); CREATE TABLE actor( actor_ID NUMBER NOT NULL, actor_fname VARCHAR2(15), actor_lname VARCHAR2(15), PRIMARY KEY(actor_ID)); CREATE TABLE film_actor( film_ID NUMBER NOT NULL, actor_ID NUMBER NOT NULL, PRIMARY KEY(film_ID, actor_ID), FOREIGN KEY(film_ID) REFERENCES film(film_ID), FOREIGN KEY(actor_ID) REFERENCES actor(actor_ID)); CREATE TABLE film_director( film_ID NUMBER NOT NULL, director_ID NUMBER NOT NULL, PRIMARY KEY(film_ID, director_ID), FOREIGN KEY(film_ID) REFERENCES film(film_ID), FOREIGN KEY(director_ID) REFERENCES director(director_ID)); INSERT INTO studio (studio_ID, studio_Name) VALUES (1, 'Paramount'); INSERT INTO studio (studio_ID, studio_Name) VALUES (2, 'Warner Bros'); INSERT INTO studio (studio_ID, studio_Name) VALUES (3, 'Film4'); INSERT INTO studio (studio_ID, studio_Name) VALUES (4, 'Working Title Films'); INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (1, 1, 'Comedy', 1, 180, 'The Wolf Of Wall Street', 2013); INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (2, 2, 'Romance', 2, 143, 'The Great Gatsby', 2013); INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (3, 3, 'Science Fiction', 3, 103, 'Never Let Me Go', 2008); INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (4, 4, 'Romance', 4, 127, 'Pride and Prejudice', 2005); INSERT INTO director (director_ID, director_fname, director_lname) VALUES (1, 'Martin', 'Scorcese'); INSERT INTO director (director_ID, director_fname, director_lname) VALUES (2, 'Baz', 'Luhrmann'); INSERT INTO director (director_ID, director_fname, director_lname) VALUES (3, 'Mark', 'Romanek'); INSERT INTO director (director_ID, director_fname, director_lname) VALUES (4, 'Joe', 'Wright'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (1, 'Matthew', 'McConnaughy'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (2, 'Leonardo', 'DiCaprio'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (3, 'Margot', 'Robbie'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (4, 'Joanna', 'Lumley'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (5, 'Carey', 'Mulligan'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (6, 'Tobey', 'Maguire'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (7, 'Joel', 'Edgerton'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (8, 'Keira', 'Knightly'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (9, 'Andrew', 'Garfield'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (10, 'Sally', 'Hawkins'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (11, 'Judi', 'Dench'); INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (12, 'Matthew', 'Macfadyen'); INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 1); INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 2); INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 3); INSERT INTO film_actor (film_ID, actor_ID) VALUES (1, 4); INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 2); INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 5); INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 6); INSERT INTO film_actor (film_ID, actor_ID) VALUES (2, 7); INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 5); INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 8); INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 9); INSERT INTO film_actor (film_ID, actor_ID) VALUES (3, 10); INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 5); INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 8); INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 11); INSERT INTO film_actor (film_ID, actor_ID) VALUES (4, 12); INSERT INTO film_director (film_ID, director_ID) VALUES (1,1); INSERT INTO film_director (film_ID, director_ID) VALUES (2,2); INSERT INTO film_director (film_ID, director_ID) VALUES (3,3); INSERT INTO film_director (film_ID, director_ID) VALUES (4,4);
Improve this question
5
  • You've not added a tag for the specific DBMS you're using. As syntax and functionality differs between them, the answer can vary. Please edit to add the relevant DBMS (SQL Server, MySQL, Oracle, or whatever) tag. Commented Mar 21, 2016 at 23:56
  • @KenWhite Do you mean oracle-sqldeveloper? Commented Mar 22, 2016 at 0:01
  • 1
    what about select film_title from film where film_len = (select max(film_len) from film); Commented Mar 22, 2016 at 0:06
  • @JerryJeremiah Yeah that works, thanks. Commented Mar 22, 2016 at 0:09
  • 1
    No. SQL Developer is a tool that you use to run SQL statements against Oracle (the DBMS). So the proper tag would be Oracle. The tool being used is irrelevant; the SQL dialect supported by the DBMS is what matters. Commented Mar 22, 2016 at 0:21

4 Answers 4

Reset to default
4

--You have to assume that there will be movies with the same runtime. 

select f.film_title from film f where film_Len = (select max(film_Len) from film)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I don't know how I didn't think of it
Performance wise, this query will be slower since it will scan the table twice.
1

You can also use ranking function to determine:

SELECT * FROM (SELECT f.film_title, rank() over(partition BY f.film_title order by f.film_len DESC) rnk from film f ) WHERE rnk = 1

If there're 2 films with the same length, they will be shown.

Improve this answer

Comments

1

You could use a pagination query:

Oracle 11g and before:

SELECT * FROM ( SELECT f.film_title FROM film f ORDER BY f.film_len DESC ) WHERE ROWNUM = 1;

Oracle 12c : Top-n Row limiting feature

SELECT f.film_title FROM film f ORDER BY f.film_len DESC FETCH FIRST 1 ROW ONLY;
Improve this answer

Comments

1 
select * from (select f.film_title from film f order by f.film_len desc) as f where rownum <= 1;

UPDATE apply where after ordering.

Improve this answer

3 Comments

@IvanGritsenko That is a completely wrong answer. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned. See How ROWNUM works in pagination query?
@Jordan5497 It is absolutely incorrect. You must do the sorting first inside a sub-query and then you need to apply ROWNUM.
@Lalit Kumar B, I've made updates. Thank you very much for pointing out.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.