sql - Find Max Value and date between date range returning mutliplie records -

sql - Find Max Value and date between date range returning mutliplie records -

i hope can help me, been working on day.

i need max value, , date , id max value associated between specific date ranges.

here code , , have tried many different version still returning more 1 id , date

select distinct bw_s.id, avs.carprod, cd_s.recorddate, cd_s.milkproduction milkprod, cd_s.waterproduction waterprod tbltest bw_s inner bring together tbltestcp cd_s with(nolock) on bw_s.id=cd_s.id , cd_s.recorddate between '08/06/2014' , '10/05/2014' inner bring together ( select id, max(carvol) carprod tbltestcp recorddate between '08/06/2014' , '10/05/2014' grouping id ) avs on avs.id = bw_s.id order id

i have table

id recorddate carprod milkprod waterprod 47790 2014-10-05 132155 0 225 47790 2014-10-01 13444 0 0 47790 2014-08-06 132111 10 100 47790 2014-09-05 10000 500 145 47790 2014-09-20 10000 800 500 47791 2014-09-20 10000 300 500 47791 2014-09-21 10001 400 500 47791 2014-08-21 20001 600 500

and result should ( max carprod)

id recorddate carprod milkprod waterprod 47790 2014-10-05 132155 0 225 47791 2014-08-21 20001 600 500

i've assumed name of table "data":

select * info data.recorddate between '2014-08-21' , '2014-10-01' order data.carprod desc limit 1;

make sure alter dates match particular requirements are.

sql r max