MySql View - Value from one column where some other column is max

Question

I have Table1 and Table2 related on Table1.ID. There can be zero or more Table2 records for a given Table1.ID. I have a view where I want to get Table2.Value where Table2.ID is max for a given Table1.ID. A friend suggested a derived table, but that requires a subquery in the from clause, and MySQL doesn't like that. Are there any other ways to do this? I tried setting up a secondary view to take the place of the subquery, but it seems very slow. I also tried using a having clause to test Table2.ID = MAX(Table2.ID), but it doesn't recognize the column unless I put it into the group by, which screws everything else up.

What version of MySQL are you using? It should be just fine with subqueries if you're using 5.x — Wade
I'm on v5.0. The docs I've read and the error I get indicate that subqueries in the from clause are not allowed in a view. Is that only in 5.0? — user168153

Bill Karwin Bill Karwin · Accepted Answer · 2009-09-03T23:13:41

SELECT t1.*, t2a.*
FROM Table1 t1
LEFT JOIN Table2 t2a
 ON (t1.table1_id = t2a.table1_id)
LEFT JOIN Table2 t2b
 ON (t1.table1_id = t2b.table1_id AND t2a.table2_id < t2b.table2_id)
WHERE t2b.table2_id IS NULL
 AND t1.table1_id = ?;

MySql View - Value from one column where some other column is max

1 Answers