I had the same problem. I wanted to create a view to show information of the most recent year, from a table with records from 2009 to 2011. Here’s the original query:
SELECT a.*
FROM a
JOIN (
SELECT a.alias, MAX(a.year) as max_year
FROM a
GROUP BY a.alias
) b
ON a.alias=b.alias and a.year=b.max_year
Outline of solution:
- create a view for each subquery
- replace subqueries with those views
Here’s the solution query:
CREATE VIEW v_max_year AS
SELECT alias, MAX(year) as max_year
FROM a
GROUP BY a.alias;
CREATE VIEW v_latest_info AS
SELECT a.*
FROM a
JOIN v_max_year b
ON a.alias=b.alias and a.year=b.max_year;
It works fine on mysql 5.0.45, without much of a speed penalty (compared to executing
the original sub-query select without any views).