mysql - Get MAX value rows from second table on join -

i have 2 tables, first 1 keeps names , second related on in = cid. need highest date row second table, once. please below clearer explanation:

table1 a

+----+-------+ | id |  name | +----+-------+ |  1 | name1 | |  2 | name2 | |  3 | name3 | |  4 | name4 | |  5 | name5 | +----+-------+ 

table2 c

+----+-------+------------+ | id | cid   | galiojaiki | +----+-------+------------+ |  1 |    1  | 2015-04-30 | |  2 |    1  | 2015-09-30 | |  3 |    1  | 2015-03-10 | |  4 |    2  | 2015-06-30 | |  5 |    2  | 2015-07-30 | |  6 |    3  | 2015-05-11 | |  7 |    4  | 2015-05-10 | +----+-------+------------+ 

expected result:

+------------+-------+ | galiojaiki |  name | +------------+-------+ | 2015-09-30 | name1 | | 2015-07-30 | name2 | | 2015-05-11 | name3 | | 2015-05-11 | name4 | +------------+-------+ 

my query:

select a.*, c.galiojaiki `y6fdt_igym_abonementai`  inner join  (      select max(galiojaiki) y6fdt_igym_sutartys ) c on c.cid= a.id group c.abonementas 

how anout simple aggregation using max?

something like

select a.name,  max(b.galiojaiki) galiojaiki `y6fdt_igym_abonementai` inner join `y6fdt_igym_sutartys` b  on a.id = b.cid group a.name 

sql fiddle demo