0

I have three tables:

MOVIE

ACT

and a linkage table

MOVIE_2_ACT connecting them via IDs.

I want to show the movies with the most actors sorted newest to old, but I don't want all of them to be from the same year.

My Mysql statement returns only movies from 2014. (there are 100+ from that year)

Is it possible to alter the SQL to return 10 movies from 2014, 10 from 2013, etc? 

 SELECT 
        COUNT(MOVIE_ID)as C, MOVIE_ID, ID, TITLE, GENRE, RELEASE_YEAR 
    FROM 
        `MOVIE_2_ACT`, MOVIE 
    WHERE 
    MOVIE.ID = MOVIE_2_ACT.MOVIE_ID 
        AND  RELEASE_YEAR <= YEAR(CURDATE()) 
        GROUP BY MOVIE_ID 
        ORDER BY RELEASE_YEAR DESC, C DESC
        LIMIT 0,10
Rid Iculous
  • 3,696
  • 3
  • 23
  • 28
  • Have you checked this? http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group – Multisync Dec 07 '14 at 09:35
  • Your GROUP BY is backwards... The general GROUP BY rule is that columns in the select list either are arguments to a set function (e.g. COUNT), or also listed in the GROUP BY clause. COUNT(MOVIE_ID) ... GROUP BY MOVIE_ID makes no sense! – jarlh Dec 07 '14 at 17:04

0 Answers0