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 :)
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 :)
