Q:

SQL How to select the value of the end of season(every three month)

Supposed I have some data as below:
    code     vol    val      num     test_date
   ------------------------------------------
1   00001    500    0.1      111      20180105
2   00001    1000   0.2      222      20180304
3   00001    200    0.1      111      20180330
4   00001    400    0.3      222      20180601
5   00001    200    0.2      333      20180630

My expected result is 
    code     vol    val      num     test_date
   ------------------------------------------
1   00001    200    0.1      111      20180330
2   00001    200    0.2      333      20180630
3   00001    200    0.2      333      20180928 -- Max(val) only 0928, there is no data in 20180930
4   00001    200    0.2      333      20181231

I would like to select the max(val) for the month in '3, 6, 9 12', how to query in MySQL, thanks  so much for any advice.

A:

You can use quarter function available in mysql.
select * from test t1
inner join (
  select  quarter(test_date) qtr, max(val) val from test
  group by quarter(test_date)) t2 on t2.val = t1.val and t2.qtr = quarter(t1.test_date)

see dbfiddle.

