How to form a MySQL query?

Question

MySQL

There is one table of the form:
1 | Vasya | 7 | 01/01/2018
2 | Vasya | 2 | 02/02/2018
3 | Petya | 2 | 01/01/2018
4 | Vova | 1 | 01/01/2018
I need to group by name, get the amount and get the most recent date in a separate column.
The result should be:
1 | Vasya | 9 | 02/02/2018
2 | Petya | 2 | 01/01/2018
3 | Vova | 1 | 01/01/2018
So far, I have only gotten to grouping with summation. =)
Please help or direct me in the right direction.

Answer 1

MAX()

Answer 2

Learn how to use MAX(), SUM(), NUMBER() in mySQL first.
Then try to emulate the constructs: Max
() over (partition by ... )
Sum() over (partition by ... )
Rank() over (partition by ... order by ... )
compared with MS SQL, Oracle and other DBMS that can use these constructs.

select b.id, b.name, b.sum_value, b.max_date -- а в самом конечной выборке выбираем только первые номера нумерации строк от каждого человека
from ( select a.id, a.name, a.sum_value, a.max_date,  -- в этом подзапросе нумеруем строки в пределах одного человека
                      @rank := IF(@name2 != a.name, 1, @rank + 1) as rnk,
                      @name2 := a.name
              from (select t.id, t.name,  -- в этом подзапросе находим сумму очков и максимум даты
                          @sum := IF(@name != name, value, @sum + value) as sum_value,
                          @max := IF(@name != name, date, @max) as max_date,
                          @name := name
                  from   
                             (select 1 id, 'Вася' Name, 7 value, STR_TO_DATE('01.01.2018', '%d.%m.%Y') date union all
                              select 2 id, 'Вася' Name, 2 value, STR_TO_DATE('02.02.2018', '%d.%m.%Y') date union all
                              select 3 id, 'Петя' Name, 2 value, STR_TO_DATE('01.01.2018', '%d.%m.%Y') date union all
                               select 4 id, 'Вова' Name, 2 value, STR_TO_DATE('01.01.2018', '%d.%m.%Y') date ) t, --исходные данные в виде таблицы t
                             (select @max := 0, @sum := 0, @name := '') p
                  order by t.name, date desc
                 ) a,
                  (select @rank := 1, @name2 := '') p2
                  order by a.name, a.sum_value desc
) b where b.rnk = 1
order by b.id, b.name