Nested order by/order by within order by in SQL

Question

I am looking to sort my sql results via an sql query with presumably some sort of nested order by/order by within an order by clause

I have the 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 I wish to obtain the following order for the 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 be sorted firstly by the score (stored only in item_no 1 for each user) descending. However any further items created by that user for the seleted term must also be picked up and inserted directly following and in item_no order.

My current query looks like this:

SELECT * FROM table WHERE term = 'man' ORDER BY SCORE DESC, ITEM_NO ASC

...however this simply results as 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 you for any suggestions.

Answer 1

SELECT *,
  (SELECT MAX(Score)
   FROM TEST t2
   WHERE t2.Term = t1.Term AND t2.User = t1.User GROUP BY t2.Term, t2. User) as max_score
FROM test t1
WHERE term = 'man'
ORDER BY max_score DESC, ITEM_NO ASC

Working DEMO

Or another solution with the same results (I think it has better performance, but you'd need to do some testing about that):

SELECT t1.*
FROM test t1
JOIN
  (SELECT t2.Term, t2.User, score FROM Test t2 WHERE t2.Item_No = 1) t3
ON t1.Term = t3.Term AND t1.User = t3.User
ORDER BY t3.score DESC, t1.Item_No;

DEMO