title: How to create differential column and select the entry with the maximal diff MySQL?
body: <p>I have small db which consist of the following tables:</p>
  
  <pre><code>  describe cast;
    +----------+---------+------+-----+---------+-------+
    | Field    | Type    | Null | Key | Default | Extra |
    +----------+---------+------+-----+---------+-------+
    | actor_id | int(11) | YES  | MUL | NULL    |       |
    | movie_id | int(11) | YES  | MUL | NULL    |       |
    | salary   | int(11) | YES  |     | NULL    |       |
    +----------+---------+------+-----+---------+-------+
  
     describe movies;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | id    | int(11)      | NO   | PRI | 0       |       |
    | title | varchar(100) | YES  |     | NULL    |       |
    | year  | int(11)      | YES  |     | NULL    |       |
    | genre | varchar(100) | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
  </code></pre>
  
  <p>And I'm trying to find which actor had the longest "pause" between two films.</p>
  
  <p>I have tried to follow this logic:
     <br>(1) Create a table which consist of the actors_id and films years
     <br>(2) Order the above table by actor name and then year
     <br>(3) Create a new column of the diff between every 
       sequential years
     <br>(4) For every actor_id find the maximal diff
     <br>(5) Select the actor_id with the maximal diff </p>
  
  <p>to do so I have first joined between the cast and movies table to get a table from the following format:</p>
  
  <pre><code>  actor_id | year
    ---------------
      11111 | 2000
      11111 | 2015
      22222 | 2001
      22222 | 2005
  </code></pre>
  
  <p>Using:</p>
  
  <pre><code>  select cast.actor_id, movies.year
    from movies 
    right join 
    cast on cast.movie_id = movies.id                                                                         
                  order by actor_id,  movies.id
  </code></pre>
  
  <p>then I have tried to create a diff column using:</p>
  
  <pre><code> select x.actor_id,
          x.year,
          x.year - coalesce(max(y.year), x.year) diff
   from (select cast.actor_id, movies.year
                  from movies 
                  right join cast on cast.movie_id = movies.id                                                                        
                  order by actor_id) x
   left 
   join (select cast.actor_id, movies.year
                  from movies 
                  right join cast on cast.movie_id = movies.id                                                                        
                  order by actor_id) y
   on y.year &lt; x.year
   group by x.actor_id
  </code></pre>
  
  <p>However i cannot group by the actor_id, getting the following Error:</p>
  
  <pre><code>Error Code: 1055. 
  Expression #2 of SELECT list is not in GROUP BY clause and contains
  nonaggregated column 'x.year' which is not functionally dependent on
  columns in GROUP BY clause;
  </code></pre>