CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Kamilos
Kamilos

Reputation: 39

DISTINCT one column

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

Upvotes: 0

Views: 104

Answers (1)

Ram
Ram

Reputation: 3091

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

Upvotes: 1

Related Questions