0

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
Kamilos
  • 39
  • 1
  • 7
  • Use group by seriesId. – k.tarkin Oct 28 '14 at 14:36
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Oct 28 '14 at 14:36
  • If I use group by series id I get first episode, no last... – Kamilos Oct 28 '14 at 14:38
  • How do you decide the last added episode? by the biggest episode.id? – Ram Oct 28 '14 at 14:47
  • Check this question, maybe it has a solution for you http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – Alex Belyaev Oct 28 '14 at 14:56

1 Answers1

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
Ram
  • 3,092
  • 10
  • 40
  • 56