title: SQLZoo "More Join Operations" #15
body: <p><strong>Edit</strong>:</p>
  
  <p><a href="http://sqlzoo.net/wiki/More_JOIN_operations" rel="nofollow noreferrer">SQLZoo More Join Operations</a> problem 15 has changed since I asked this question. It now states: "List the films released in the year 1978 ordered by the number of actors in the cast, then by title."</p>
  
  <p>I give thanks to all who tried to help with the original phrasing. I've updated the accepted answer to match the current problem.</p>
  
  <p><strong>Original Question</strong>:</p>
  
  <p>I'm trying to solve problem number 15 under <a href="http://sqlzoo.net/wiki/More_JOIN_operations" rel="nofollow noreferrer">SQLZoo More Join Operations</a> (I'm brushing up for an interview tomorrow)</p>
  
  <p>The question is: "List the 1978 films by order of cast list size. "</p>
  
  <p>My answer is:</p>
  
  <pre><code>SELECT movie.title, count(casting.actorid) 
     FROM movie INNER JOIN casting 
        ON movie.id=casting.movieid
     WHERE movie.yr=1978
  GROUP BY movie.id
  ORDER BY count(casting.actorid) desc
  </code></pre>
  
  <p>This is essentially identical to the answer given by <a href="http://www.gideondsouza.com/blog/solutions-to-sqlzoo.net-more-join-operation-questions-movie-database" rel="nofollow noreferrer">Gideon Dsouza</a> except that my solution does not assume titles are unique:</p>
  
  <pre><code>SELECT m.title, Count(c.actorid)
    FROM casting c JOIN movie m ON
      m.id = c.movieid
    WHERE m.yr = 1978
  GROUP BY m.title
  ORDER BY Count(c.actorid) DESC
  </code></pre>
  
  <p>Neither my solution nor his is marked correct.</p>
  
  <p>The results from my solution and the "correct" solution are given at the end. My list has two movies ("Piranha" and "The End") that the "correct" solution lacks. And the "correct" solution has two movies ("Force 10 From Navarone" and "Midnight Express") that mine lacks.</p>
  
  <p>Since these movies are all in the smallest cast size, I hypothesized that SQLZoo is cutting off the query at 50 rows and it was an ordering irregularity that causes the difference. However, I tried adding <code>,fieldname</code> to the end of my <code>order by</code> clause for all values of <code>fieldname</code> but none yielded an identical answer.</p>
  
  <p>Am I doing something wrong or is SQLZoo broken?</p>
  
  <p><hr></p>
  
  <h2>Result Listings</h2>
  
  <p>My solution yields (after using libreoffice to make a fixed width column):</p>
  
  <pre>
  The Bad News Bears Go to Japan   50
  The Swarm                        37
  Grease                           28
  American Hot Wax                 27
  The Boys from Brazil             26
  Heaven Can Wait                  25
  Big Wednesday                    21
  Orchestra Rehearsal              19
  A Night Full of Rain             19
  A Wedding                        19
  The Cheap Detective              19
  Go Tell the Spartans             18
  Superman                         17
  Movie Movie                      17
  The Driver                       17
  The Cat from Outer Space         17
  Death on the Nile                17
  The Star Wars Holiday Special    17
  Blue Collar                      16
  J.R.R. Tolkien's The Lord of the 16
  Ice Castles                      16
  International Velvet             16
  Coming Home                      15
  Revenge of the Pink Panther      15
  The Brink's Job                  15
  David                            15
  The Chant of Jimmie Blacksmith   15
  The Water Babies                 15
  Violette Nozière                 15
  Occupation in 26 Pictures        15
  Without Anesthesia               15
  Bye Bye Monkey                   15
  Alexandria... Why?               15
  Who'll Stop The Rain             15
  Gray Lady Down                   15
  Damien: Omen II                  14
  The Empire of Passion            14
  Bread and Chocolate              14
  I Wanna Hold Your Hand           14
  Closed Circuit                   14
  Almost Summer                    13
  Goin' South                      13
  An Unmarried Woman               13
  The Left-Handed Woman            13
  Foul Play                        13
  The End                          12
  California Suite                 12
  In Praise of Older Women         12
  Jaws 2                           12
  Piranha                          12
  </pre>
  
  <p>The correct answer is given as:</p>
  
  <pre>
  The Bad News Bears Go to Japan   50
  The Swarm                        37
  Grease                           28
  American Hot Wax                 27
  The Boys from Brazil             26
  Heaven Can Wait                  25
  Big Wednesday                    21
  A Wedding                        19
  A Night Full of Rain             19
  Orchestra Rehearsal              19
  The Cheap Detective              19
  Go Tell the Spartans             18
  Superman                         17
  The Star Wars Holiday Special    17
  Death on the Nile                17
  The Cat from Outer Space         17
  Movie Movie                      17
  The Driver                       17
  Blue Collar                      16
  Ice Castles                      16
  J.R.R. Tolkien's The Lord of the 16
  International Velvet             16
  Coming Home                      15
  The Brink's Job                  15
  Gray Lady Down                   15
  Bye Bye Monkey                   15
  Without Anesthesia               15
  Violette Nozière                 15
  The Water Babies                 15
  Revenge of the Pink Panther      15
  Who'll Stop The Rain             15
  Alexandria... Why?               15
  Occupation in 26 Pictures        15
  David                            15
  The Chant of Jimmie Blacksmith   15
  The Empire of Passion            14
  Damien: Omen II                  14
  Closed Circuit                   14
  Bread and Chocolate              14
  I Wanna Hold Your Hand           14
  An Unmarried Woman               13
  Almost Summer                    13
  Goin' South                      13
  Foul Play                        13
  The Left-Handed Woman            13
  Jaws 2                           12
  California Suite                 12
  In Praise of Older Women         12
  Force 10 From Navarone           12
  Midnight Express                 12
  </pre>