MySql left join on several regs

Question

Hi there! I have this table1

idproduct(PK) | date_to_go
1               2010-01-18
2               2010-02-01
3               2010-02-21
4               2010-02-03

and this other table2 that controls date_to_go updates

id | idproduct(FK) | prev_date_to_go | date_to_go | update_date
1    1               2010-01-01        2010-01-05   2009-12-01
2    1               2010-01-05        2010-01-10   2009-12-20
3    1               2010-01-10        2010-01-18   2009-12-20
4    3               2010-01-20        2010-02-03   2010-01-05

So, in this example, for table1.idproduct #1 2010-01-18 is the actual date_to_go and 2010-01-01 (table2.prev_date_to_go, first reg) is the original date_to_go .

using this query

select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go 
from table1 v 
left join produto_datas p on p.idproduto = v.idproduto
group by (v.idproduto)
order by v.idproduto

can I assume that original_date_to_go will be the first related reg of table2?

idproduct | date_to_go | original_date_to_go 
1           2010-01-18   2010-01-01
2           2010-02-01   NULL
3           2010-02-21   2010-01-20 
4           2010-02-03   NULL 

Answer 1

No, you cannot assume that you will always get the value of prev_date_to_go from the first row. When you select a column that isn't listed in your GROUP BY, the values you could get back could be the value from any row. See the MySQL documentation GROUP BY and HAVING with Hidden Columns for more information:

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Answer 2

Well, I used a subquery.

select v.idproduct, v.date_to_go,  (select p.prev_date_to_go from table2 p where p.idproduct=v.idproduct order by idtable2 asc limit 1)  original_date_to_go from table1 v group by (v.idproduct) order by v.idproduct