Nested order by/order by within order by in SQL -

i looking sort sql results via sql query presumably sort of nested order by/order within order clause

i have following data:

term      user      item_no     score man       sam       2           null man       sam       1           170 man       jerry     1           100  man       jerry     2           null man       sam       3           null 

and wish obtain following order results:

term      user      item_no     score man       sam       1           170 man       sam       2           null man       sam       3           null man       jerry     1           100 man       jerry     2           null 

the results must sorted firstly score (stored in item_no 1 each user) descending. further items created user seleted term must picked , inserted directly following , in item_no order.

my current query looks this:

select * table term = 'man' order score desc, item_no asc 

...however results follows:

term      user      item_no     score man       sam       1           170 man       jerry     1           100 man       sam       2           null man       jerry     2           null man       sam       3           null 

thank suggestions.

select *,   (select max(score)    test t2    t2.term = t1.term , t2.user = t1.user group t2.term, t2. user) max_score test t1 term = 'man' order max_score desc, item_no asc 

working demo

or solution same results (i think has better performance, you'd need testing that):

select t1.* test t1 join   (select t2.term, t2.user, score test t2 t2.item_no = 1) t3 on t1.term = t3.term , t1.user = t3.user order t3.score desc, t1.item_no; 

demo