CODEWITHSUNDEEP
sql
user2368457
user2368457

Reputation: 75

SQL: Selecting the MAX within subqueries

OK so i'm tasked with finding the latest release with a particular actor in it. There are multiple tables, One with basic movie information (title, yearreleased), one with actor names with their Names, Starid, and another with the movienum they acted in, having Movienum, Starid.

Basically i've gotten 75% of the way and i'm trying to return only one movie that is the latest by a particular actor. This is my query so far

  SELECT MovieTitle, Datereleased
  FROM MovieList
  WHERE Movienumber IN (SELECT Movienumber FROM ActsIn Where Starids IN (SELECT Starids FROM Stars       WHERE Familyname = 'Tarintino'))

Now when i run it i get 3 rows of 3 movies with their according information. the first column is The movie title, and the second is Yearreleased. What can i do to the query to return only the latest movie? (eg MAX Yearreleased)

Upvotes: 1

Views: 79

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

Here's the code, now study it and look up all the syntax you don't know:

Select top 1 MovieTitle, DateReleased From MovieList
Inner Join
ActsIn
on ActsIn.MovieNumber = MovieList.MovieNumber
Inner Join
Stars
on Stars.Starids = ActsIn.StarIds
Where
Stars.FamilyName = 'Tarantino'
ORDER BY DateReleased DESC

Here's an alternative way to get a list of all the latest releases by all the actors:

    select Stars.*, MovieTitle, DateReleased From MovieList inner join (
    Select MAX(DateReleased) AS DateReleased, StarIDs From MovieList
        Inner Join
        ActsIn
        on ActsIn.MovieNumber = MovieList.MovieNumber
    Group by StarIDs )  lastMovie on lastMovie.DateReleased = MovieList.DateReleased
    Inner join Stars on Stars.StarIDs = lastMovie.starIDs
Where FamilyName = 'Tarantino'

Upvotes: 4

Related Questions