We have a table with three columns :id int, Num_challenge int и len int . Where Num_challange is the number of the competition (in order of priority), and len is the length of the jump. How to build a query that would give out id 3 people who had the biggest difference between jumping at their last competition and jumping at their first?
Urbani7 Urbani7
6 3
- Is id a person's id? - splash58
- Yes, this is so .... - Urbani7
|
1 answer
turned out to be difficult for me
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 
splash58 splash58
5.145 2 7 18
- This is already half an hour) - Urbani7
- Well, somehow too long. and a big base? - splash58
- 14 thousand records. - Urbani7
- yes not so much - splash58
- I made a mistake, 14 thousand people, and 1,4kk records - Urbani7
|