Как написать SQL-запрос?

SELECT `t`.`id`, `m`.`name`, `m`.`date`
  FROM (
    SELECT MAX(`date`) AS `date`, `name`
      FROM `table`
      GROUP BY `name`
  ) AS `m`
  JOIN `table` AS `t`
    ON `t`.`date` = `m`.`date` AND `t`.`name` = `m`.`name`

MariaDB [test]> select * from t1;
+------+-------+------+
| id   | name  | date |
+------+-------+------+
|    1 | Dima  | 2017 |
|    2 | Dima  | 2018 |
|    3 | Sasha | 2017 |
|    4 | Sasha | 2018 |
+------+-------+------+
4 rows in set (0.00 sec)

MariaDB [test]> SELECT id,name,MAX(date) FROM t1 GROUP BY name;
+------+-------+-----------+
| id   | name  | MAX(date) |
+------+-------+-----------+
|    1 | Dima  | 2018      |
|    3 | Sasha | 2018      |
+------+-------+-----------+
2 rows in set (0.00 sec)

SELECT id, name, date FROM table GROUP BY name ORDER BY date DESC;