title: How to find the second largest value within certain row?
body: <p>I have two tables, movies(id, name, year, rating) and movies_genres(movie_id, genre), I want to find the id of all second top rated movies within each genre(not global), but when I wrote this</p>
  
  <pre><code>select MG.genre, M.id
  from movies_genres MG inner join movies M on MG.movie_id = M.id
  where M.rating = 
  (select max(rating) from 
  (select rating from movies M2 inner join movies_genres MG2 on M2.id = MG2.movie_id where MG2.genre = MG.genre) 
  where rating &lt; 
  (select max(rating) from 
  (select rating from movies M3 inner join movies_genres MG3 on M3.id = MG3.movie_id where MG3.genre = MG.genre)))
  
  order by MG.genre;
  </code></pre>
  
  <p>I got an error, it said that the MG.genre in Line 5 is a invalid identifier.</p>