Sort by feild from a joined table

Databases
1

I hav to tables like this:

CREATE TABLE episodes (
  id int(11) NOT NULL AUTO_INCREMENT,
  series_id int(11) NOT NULL,
  episode_name varchar(50) NOT NULL,
  episode int(4) NOT NULL,
  first_aired date DEFAULT NULL,
  PRIMARY KEY ( id )
)

CREATE TABLE series (
  id int(11) NOT NULL AUTO_INCREMENT,
  tvdb_id int(10) NOT NULL,
  actors varchar(200) NOT NULL,
  first_aired date NOT NULL,
  genre varchar(200) NOT NULL,
  series_name varchar(50) NOT NULL,
  PRIMARY KEY ( id )
)

When I show all the series I want to sort by first_aired in the episodes table.

I have tested with this:

SELECT
	s.*,
	MAX(e.first_aired) AS last_aired
FROM
	series s
LEFT JOIN
	episodes e ON e.series_id = s.id
WHERE
	e.first_aired < CURDATE()
GROUP BY
	s.id
ORDER BY
	last_aired DESC

I do not understand what is wrong. It falls almost right, but some series that should perhaps end up in tenth place could end up in 3rd place and so on. Is it possible to solve it?

2

First of all, if you do a WHERE condition on the second table like that, the LEFT JOIN becomes an INNER JOIN, and you won’t get any series that don’t have a row in the episodes table. If that’s ok for you, then you might as well make that LEFT JOIN an INNER join.
Secondly, could you give a data example of your problem? That is, give the data in your tables, the result your query is giving you, and the result you want?