MySQL Group by, Order by Two Columns

Question

I want to get the last episode name of each movie

Table structure:

+---------+------------+---------------+
|     id  |  movie_id  |  episode_name |
+---------+------------+---------------+
|      1  |         1  |             1 |
|      2  |         1  |             2 |
|      3  |         1  |             3 |
|      4  |         2  |             1 |
|      5  |         3  |             1 |
|      6  |         2  |             2 |
|      7  |         1  |             4 |
|      8  |         2  |             3 |
|      9  |         4  |             1 |
+---------+------------+---------------+

Query 1:

SELECT * FROM episode
group by movie_id
order by id desc, episode_name desc LIMIT 10

Query 2:

SELECT * FROM episode
WHERE movie_id IN (select movie_id from episode group by movie_id)
group by movie_id
order by episode_name desc

But the output doesn't what i want, maybe i do it wrong way

+--------+------------+---------------+
|  id    |  movie_id  |  episode_name |
+--------+------------+---------------+
|     9  |         4  |             1 |
|     5  |         3  |             1 |
|     4  |         2  |             1 |
|     1  |         1  |             1 |
+--------+------------+---------------+

My desired result is:

+---------+------------+---------------+
|     id  |  movie_id  |  episode_name |
+---------+------------+---------------+
|      9  |         4  |             1 |
|      8  |         2  |             3 |
|      7  |         1  |             4 |
|      5  |         3  |             1 |
+---------+------------+---------------+

Answer 1

If you only want the last episode_name and if I follow the logic of your table, you can try to use MAX() in your SELECT query, so you will only get the MAX result of your column "episode_name" (mode info here)

SELECT id, movie_id, MAX(episode_name) 
FROM table GROUP BY movie_id 
ORDER BY id DESC

Is it what your are looking for?

Answer 2

This is a pretty common problem, which we can handle in MySQL by joining the episode table to a subquery which finds the latest episode for each movie. This approach gives us the full matching latest records, including the id column, which we can then use to order the result set.

SELECT e1.*
FROM episode e1
INNER JOIN
(
    SELECT movie_id, MAX(episode_name) AS max_episode_name
    FROM episode
    GROUP BY movie_id
) e2
    ON e1.movie_id = e2.movie_id AND
       e1.episode_name = e2.max_episode_name
ORDER BY
    e1.id DESC;

Demo