sql 对查询结果中的某一部分排序

请问sql怎么只对查询结果中的某一部分进行排序？比如说：查找学号值大于10的学生学号，并只对学号值大于100的记录降序排列。

ORACLE 语法

SELECT
    *
FROM
    (
    SELECT
        s.studentid ,
        CASE
            WHEN studentid >100 THEN studentid
            ELSE NULL
        END AS rnt
    FROM
        student s
    WHERE
        s.studentid> 10 ) tt
ORDER BY
    rnt DESC NULLS LAST

用子查询in

select * from Student stu
where stu.id  >100 and stu.id in
(select stu2.id form Student stu2 where stu2.id>10)
order by stu.id desc   

select a.学号，a.* from student a where a.学号>100 order by 学号 desc union select b.学号，b.* from student b where b.学号>=10 and b.学号<=100;

DECLARE @a TABLE (id INT IDENTITY(1,1),a INT,score INT)
INSERT @a SELECT 1,20
UNION ALL SELECT 2,10
UNION ALL SELECT 3,40
UNION ALL SELECT 4,50
UNION ALL SELECT 5,90
UNION ALL SELECT 6,70
UNION ALL SELECT 7,37
UNION ALL SELECT 8,12
UNION ALL SELECT 9,66
UNION ALL SELECT 10,67
SELECT * FROM @a 
WHERE score >15
ORDER BY CASE WHEN score >60 THEN score ELSE -id END DESC

局部排序是啥含义，小于100的升序 大于100的降序 ，这两者要怎么排序？