I need to ask a question in mysql where I have two sales and purchase tables these have the date field and I want to take c.fechacompra < v.fechaventa The problem is that I have many purchase date less than the date-of-sale and the query gets the lowest of all and not the least immediate, that is, the most recent date among the previous ones. date sale .
Using the MAX SQL function you can do what you need.
I understand from your question that you have 2 tables and from that you only need the purchase date closest to the first sale date (it does not make much sense, I think you should update your question) and you must match with join with the products. To do that:
select Max(c.fechacompra) as UltimaCompra
from compras c
join ventas v on c.ProductoID = v.ProductoID
where c.fechacompra < v.fechaventa
From what I have understood of your question, try adding a ORDER BY (CAMPO) (ASC/DESC) , with this you can choose the order to get the lowest, but if you also want the result of the query to return an exclusive record you can add FETCH FOR 1 ROW ONLY .