如何使用SQL组筛选具有最大日期值的行

I have the following table

我有下表

CREATE TABLE Test
    (`Id` int, `value` varchar(20), `adate` varchar(20))
;

INSERT INTO Test
    (`Id`, `value`, `adate`)
VALUES
    (1, 100, '2014-01-01'),
    (1, 200, '2014-01-02'),
    (1, 300, '2014-01-03'),
    (2, 200, '2014-01-01'),
    (2, 400, '2014-01-02'),
    (2, 30 , '2014-01-04'),
    (3, 800, '2014-01-01'),
    (3, 300, '2014-01-02'),
    (3, 60 , '2014-01-04')
;
CREATE TABL