I have table with 3 column: 'id' int, 'num_challange' int and 'len' int with people id, and length of their jumps on each challange(not every human jumps at all challanges). How to make an sqlite query to find 3 people with largest difference between len on their last challange and there first challange?
I tried this:
select tt.id, abs(t1.len-t2.len) df
from
(select id, min(Num_challenge) min_nc, max(Num_challenge) max_nc
from t group by id) tt
join t t1 on t1.id=tt.id and t1.Num_challenge=tt.min_nc
join t t2 on t2.id=tt.id and t2.Num_challenge=tt.max_nc
order by df desc
limit 3
But it has not calculated for 70 minuts(table has 14000 lines).
Aucun commentaire:
Enregistrer un commentaire