How to get the most distant date in Mysql?

Question

I have the following tables

             boi
-------------------------------
id | peso |    data_pesagem   | 
-------------------------------
1  | 175  |     2019-01-02    |
2  | 200  |     2019-01-03    |
-------------------------------


         boi_manejo
---------------------------
fk_boi | peso |    data   | 
---------------------------
   1   | 213  | 2019-02-05|
   2   | 222  | 2019-02-03|
   1   | 213  | 2019-01-20|
   2   | 222  | 2019-01-29|
---------------------------

How can I easily search for the date farthest from the ox ID 1 in the handling table, in relation to its weighing date in the first table, for example?

Answer 1

Sort your results by the difference between the table date boi and the date of the table boi_manejo. Then limit the number of results to just one.

Schema (Mysql v5.7)

CREATE TABLE boi (
  id           INTEGER,
  peso         NUMERIC(15, 2),
  data_pesagem DATE
);

INSERT INTO boi(id, peso, data_pesagem)
        VALUES (1, 175, '2019-01-02'),
               (2, 200, '2019-01-03');

CREATE TABLE boi_manejo (
  fk_boi INTEGER,
  peso   NUMERIC(15, 2),
  data   DATE
);

INSERT INTO boi_manejo(fk_boi, peso, data)
               VALUES (1, 213, '2019-02-05'),
                      (2, 222, '2019-02-03'),
                      (1, 213, '2019-01-20'),
                      (2, 222, '2019-01-29');

---

Query

SELECT b.id,
       bm.peso,
       bm.data
  FROM boi b
 INNER JOIN boi_manejo bm ON bm.fk_boi = b.id
 WHERE b.id = 1
 ORDER BY DATEDIFF(b.data_pesagem, bm.data) DESC
 LIMIT 1;

Resulting in

| id  | peso | data       |
| --- | ---- | ---------- |
| 1   | 213  | 2019-01-20 |

---

See working on DB Fiddle.

---

DATEDIFF

Returns expr1 − expr2 Expressed as a value in days from one date to the other.

In free translation:

returns expr1 − expr2 as a value in days from one date to the other.