Mysql : ORDER BY Twice

Question

EDITED : I need users to be ordered by their global progression [order by avg(progression) DESC] and the lines of details orderd by progression.

Example :

USER 1 campaign 1 progression 100 
USER 1 campaign 2 progression 100
USER 2 campaign 1 progression 95 
USER 2 campaign 2 progression 80
USER 3 campaign 1 progression 70 
USER 3 campaign 2 progression 25

I run the following query:

SELECT p.usersid, p.current_campaign, p.campaign_progression
FROM progression_campagne p 
JOIN USER u ON  p.usersid=u.id AND u.organismsid=10 
GROUP BY p.usersid, p.current_campaign
ORDER BY p.usersid DESC,p.campaign_progression DESC;

To get result like this :

But I got the following result:

Any help?

Answer 1

Add an average column and use this as the first sort column

drop table if exists t;
create table t(user int, campaign int , progression int);
insert into t values
(1 , 1 , 100) ,
(1 , 2 , 100),
(2 , 1 , 95 ),
(2 , 2 , 80),
(3 , 1 , 70 ),
(3 , 2 , 25);

select user,campaign, progression, (select avg(t1.progression) from t t1 where t1.user = t.user group by t1.user) average
from t
order by average desc,user desc,progression desc;

+------+----------+-------------+----------+
| user | campaign | progression | average  |
+------+----------+-------------+----------+
|    1 |        1 |         100 | 100.0000 |
|    1 |        2 |         100 | 100.0000 |
|    2 |        1 |          95 |  87.5000 |
|    2 |        2 |          80 |  87.5000 |
|    3 |        1 |          70 |  47.5000 |
|    3 |        2 |          25 |  47.5000 |
+------+----------+-------------+----------+