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);
Poena
  • 79
  • 1
  • 11
  • 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. – Ken White Mar 21 '16 at 23:56
  • @KenWhite Do you mean oracle-sqldeveloper? – Poena Mar 22 '16 at 00:01
  • 1
    what about `select film_title from film where film_len = (select max(film_len) from film);` – Jerry Jeremiah Mar 22 '16 at 00:06
  • @JerryJeremiah Yeah that works, thanks. – Poena Mar 22 '16 at 00: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. – Ken White Mar 22 '16 at 00:21

4 Answers4

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)
Vincent
  • 792
  • 5
  • 13
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.

Ivan Gritsenko
  • 3,910
  • 2
  • 17
  • 30
  • 2
    @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?](http://stackoverflow.com/a/30321788/3989608) – Lalit Kumar B Mar 22 '16 at 04:58
  • 3
    @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 Mar 22 '16 at 05:00
  • @Lalit Kumar B, I've made updates. Thank you very much for pointing out. – Ivan Gritsenko Mar 22 '16 at 10:36
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.

Nguyen
  • 31
  • 5
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;
Community
  • 1
  • 1
Lalit Kumar B
  • 43,345
  • 10
  • 82
  • 112