如何从两个查询的联合中选择前n个，需要根据单个查询对结果顺序进行排序？

case

select  top 4 *
from    Usernames
where   Name like \'%Bob%\'
order by
        case when Name = \'Bob\' then 1 else 2 end

(Name)

select  top 4 *
from    (
        select  1 as SortOrder
        ,       *
        from    Usernames
        where   Name = \'Bob\'
        union all
        select  2
        ,       *
        from    Usernames
        where   Name like  \'%Bob%\'
                and Name <> \'Bob\'
                and 4 >
                (
                select  count(*)
                from    Usernames
                where   Name = \'Bob\'
                )
        ) as SubqueryAlias
order by
        SortOrder

WHERE Name LIKE \'Bob%\'

\'%Bob\'

SELECT TOP 4 a.* FROM
(
    SELECT *, 1 AS Priority from Usernames WHERE Name = \'Bob\'
    UNION
    SELECT *, 2 from Usernames WHERE Name LIKE \'%Bob%\'
) AS a
ORDER BY Priority ASC

SELECT TOP 4 * FROM (
SELECT 1 as Rank , I, name  FROM Foo  WHERE Name = \'Bob\' 
UNION ALL
SELECT 2 as Rank,i,name  FROM Foo  WHERE Name LIKE \'%Bob%\' 
) as Q1
ORDER BY Q1.Rank, Q1.I

SET ROWCOUNT 4

SELECT * from Usernames WHERE Name = \'Bob\'
UNION
SELECT * from Usernames WHERE Name LIKE \'%Bob%\'

SET ROWCOUNt 0

SELECT TOP 4 a.* FROM
(
    SELECT TOP 4 *, 1 AS Sort from Usernames WHERE Name = \'Bob\'
    UNION ALL
    SELECT TOP 4 *, 2 AS Sort from Usernames WHERE Name LIKE \'%Bob%\' and Name <> \'Bob\'
) AS a
ORDER BY Sort

SELECT Field_1 as [@attr_1]

SELECT top 1 a.SupervisorName as [@SupervisorName]
FROM
(
    SELECT (FirstNames + \' \' + LastName) AS [SupervisorName],1 as OrderingVal
    FROM ExamSupervisor SupervisorTable1

    UNION ALL

    SELECT (FirstNames + \' \' + LastName) AS [SupervisorName],2 as OrderingVal
    FROM ExamSupervisor SupervisorTable2

) as a
ORDER BY a.OrderingVal ASC
FOR XML PATH(\'Supervisor\')