sql - MySQL proportionate subset of returned rows -

i have query so

select     t.trial,     count(*),     max(d.value) data d inner join trial_info t      on d.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp group t.trial; 

which returns

t.trial | count(*) | max(d.value) --------------------------------- 1       | 80       | 176 2       | 63       | 219 3       | 49       | 109 4       | 67       | 155 

one row of d represents 1 second, condensing on t.trial means count(*) return number of seconds in each trial, , max(d.value) largest value observed in seconds.

question: how can whittle results return middle 50% of each trial and max value in time? want throw out first 25% of trial time last 25%. subquery skills aren't hot...

here idea far. substituting join works when computedvalue set static number. want computedvalue percentage of rows returned (displayed in count(*) result column above).

inner join trial_info t on d.instance_timestamp between     date_add(t.trial_start_timestamp, interval computedvalue second) ,     date_sub(t.trial_end_timestamp, interval computedvalue second) 

use timestampdiff 0.25*interval in second t.trial_start_timestamp t.trial_end_timestamp , round integer using floor:

floor(timestampdiff(second,t.trial_start_timestamp,t.trial_end_timestamp)*0.25) 

so join condition is:

inner join trial_info t on d.instance_timestamp between     date_add(t.trial_start_timestamp, interval floor(timestampdiff(second,t.trial_start_timestamp,t.trial_end_timestamp)*0.25) second) ,     date_sub(t.trial_end_timestamp, interval floor(timestampdiff(second,t.trial_start_timestamp,t.trial_end_timestamp)*0.25) second) 

edit:

if 50% based on number of trials, not timestamp, solution use limit , offset in subquery boundary 25% 75% number of trial d.instance_timestamp:

d.instance_timestamp between (select d1.instance_timestamp data d1 d1.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp order d1.instance_timestamp asc limit 1 offset (select floor(0.25 * count(*)) data d2 d2.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp))  ,  (select d3.instance_timestamp data d3 d3.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp order d3.instance_timestamp asc limit 1 offset (select floor(0.75 * count(*)) data d4 d4.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp)) 

however, mysql not support subquery value limit , offset. in mysql, need compute instance_timestamp @ 25th , 75th percentiles using substring_index , group_concat:

    d.instance_timestamp between      (select substring_index(substring_index(group_concat(d1.instance_timestamp order d1.instance_timestamp separator ','), ',' , 0.25 * count(*) + 1), ',', -1)  data d1 d1.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp)      ,      (select substring_index(substring_index(group_concat(d2.instance_timestamp order d2.instance_timestamp separator ','), ',' , 0.75 * count(*) + 1), ',', -1)  data d2 d2.instance_timestamp between t.trial_start_timestamp , t.trial_end_timestamp)