mardi 3 mai 2016

How to make an sqlite query to find..?

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