Nested order by/order by within order by in SQL

sql

Solution

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

Problem

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.

Original source