GROUP BY query not ordering as expected

Question

I don't know why I can't make this query work the way I want, I hope you can enlighten me.

   SELECT e.id,
          e.num,
          e.name,
          s.name as s_name,
          t.num as t_num,
          e.show_id  
     FROM s_episodes e  
LEFT JOIN shows s ON s.id = e.show_id  
LEFT JOIN s_seasons t ON t.id = e.season_id  
 GROUP BY e.show_id  
 ORDER BY e.dateadded DESC

dateadded is an int (unix timepost). e.num, t.num are also int.

Is is supposed to show the latest episodes added, but when the GROUP BY groups the rows, the episodes listed aren't the latest ones but the first episode (episode number indicated by e.num) of the latest season (season number indicated by t_num) of a show (however, shows are ordered by dateadded based on the latest episodes, but the episode listed is the first one of the season, not the last added).

Any ideas?

I hope I made myself clear, sorry for any english mistakes. Thanks.

Answer 1

you need to first determine the most recent episode added for the given show. Then you can add the rest of the data.

SELECT a.dateadded,
      e.id, 
          e.num, 
          e.name, 
          s.name as s_name, 
          t.num as t_num, 
          e.show_id   

from          
(select max(dateadded) maxdate, show_id
     FROM s_episodes 
     group by show_id)a
inner join s_episodes e
      on e.show_id = a.show_id
      and e.dateadded = a.maxdate
LEFT JOIN shows s ON s.id = e.show_id   
LEFT JOIN s_seasons t ON t.id = e.season_id 

Answer 2

When you group by by one field and the resulting field is not a part of it (or in your case the order by field) the outcome is unpredictable.

You need to either add it to group by or in your case, use aggregate function:

ORDER BY MAX(e.dateadded) DESC

EDIT: do you really need the LEFT JOINs or those could aswell be regular inner joins?

Since you need also to to display the fields from latest episode you may try:

   SELECT e.id,
          e.num,
          e.name,
          s.name as s_name,
          t.num as t_num,
          e.show_id  
     FROM shows s
     JOIN s_episodes e ON e.id = 
              (SELECT id 
                 FROM s_episodes 
                WHERE show_id = s.id 
                ORDER BY dateadded DESC LIMIT 1)
LEFT JOIN s_seasons t ON t.id = e.season_id  
 ORDER BY e.dateadded DESC