How to aggregate data without group by

How to aggregate data without group by



I am having a little bit of a situation here.

The environment

I have a database for series here. One table for the series itself, one

for the season connected to the series table, one for the episodes

connected to the seasons table. Since there are air dates for different

countries I have another table called 'series_data` which looks like the

following:

CREATE TABLE IF NOT EXISTS `episode_data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`episode_id` int(11) NOT NULL,

`country` char(3) NOT NULL,

`title` varchar(255) NOT NULL,

`date` date NOT NULL,

`tba` tinyint(1) NOT NULL,

PRIMARY KEY (`id`),

KEY `episode_id` (`episode_id`),

KEY `date` (`date`),

KEY `country` (`country`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I am trying to collect the last aired episodes from each series in the

database using the following query:

SELECT

*

FROM

`episode_data` ed

WHERE

`ed`.`date` < CURDATE( ) &&

`ed`.`date` != '1970-01-01' &&

`ed`.`series_id` = 1

GROUP BY

`ed`.`country` DESC

ORDER BY

`ed`.`date` DESC

Since I have everything normalized I changed 'episode_id' with 'series_id'

to make the query less complicated.

What I am trying to accomplish

I want to have the last aired episodes for each country which are actually

announced (ed.date != '1970-01-01') as the returning result of one query.

What's the problem

I know now (searched google, found not for me working answers here), that

the ordering takes place AFTER grouping, so my "date" ordering is

completly useless.

The other problem is that the query above is working, but always takes

those entries with the lowest id matching my conditions, because those are

the first ones in the tables index.

What is the question?

How may accomplish the above. I do not know if the grouping is the right

way to do it. If there is no "one liner", I think the only way is a sub

query which I want to avoid since this is as far as I know slower than a

one liner with the right indexes set.

Hope in here is everything you need :)