Nested order by order in SQL order

Question

I am looking to sort my sql results through a sql query with supposedly some sort of nested order by / order within an order by proposal

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 want to get 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 

Results should be sorted first by invoice (stored only in item_no 1 for each user). However, any additional items created by this user for a specified period must also be selected and inserted immediately after and in the order item_no.

My current request looks like this:

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

... however, it just looks like this:

 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 

Thanks 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 

Work DEMO

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

 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