将一个顶部单行添加到查询结果集的有序结果中。

Question

我有一个非常简单的要求。

SELECT NULL as ProjectId, 'All' as ProjectName

UNION

(
  SELECT  Project.ProjectId,  Project.ProjectName
  FROM Project Order by 2
)

条目的原始顺序：

ProjectId       ProjectName     
 24               Beta           
 56               Alpha          
 57               Gamma          
 120              Aap            

预期结果集：

ProjectId      ProjectName         
______________________________
NULL           All                 
120            Aap                 
56             Alpha               
24             Beta                
57             Gamma              

我需要的是：我想在查询结果集中有序的顶部添加一行。

问题：

1. Subquerys are not allowed to have Order By clause
2. Doing Top 100 Percent destroys the order, and row having 'All' doesn't come at top

3. Declaring a Table variable inserting all entries in order and then performing union on this table i.e.

    Select NULL as ProjectId, 'All' as ProjectName...
     UNION
    select * from @myTable
   

   again destroys the order

帮帮我，拜托。

- user1186860

Answer 1

您可以订购UNION的结果，但需要提供额外的列来标识数据来自哪里 - 这里是SortOrder：

SELECT NULL as ProjectId, 'All' as ProjectName, 1 SortOrder
UNION ALL
SELECT  Project.ProjectId,  Project.ProjectName, 2 SortOrder
FROM Project
order by SortOrder, ProjectName

请注意，我将UNION替换为UNION ALL，因为您不需要对结果集进行DISTINCT操作。

- Nikola Markovinović