MySQL: View with Subquery in the FROM Clause Limitation

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:

  1. create a view for each subquery
  2. 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).

Leave a Comment