DISTINCT one column

Question

Hello,

I have two tables:

episodes:

• id
• episode
• season
• series
• title

series:

• id
• name

I would like to receive last added episodes (without repeating series)

For example: I added two episode to Walking Dead and one episode to House M.D.

So, result should be:

Episode 2 - Walking Dead

Episode 1 - House M.D.

My query at this moment is:

SELECT *, e.id AS episodeId, s.id AS seriesId 
FROM episodes AS e 
LEFT JOIN series AS s ON e.series = s.id 
ORDER BY e.id DESC LIMIT 25

Answer 1

Try using MAX(e.id)

SELECT MAX(e.id) AS episodeId, s.id AS seriesId 
FROM episodes AS e 
LEFT JOIN series AS s ON e.series = s.id 
GROUP BY s.id

If you need more columns then use a join

SELECT * 
FROM episodes AS ep
JOIN
(SELECT MAX(e.id) AS episodeId, s.id AS seriesId 
FROM episodes AS e 
LEFT JOIN series AS s ON e.series = s.id 
GROUP BY s.id) temp
ON temp.episodeId =ep.id