Add a Top Single Row to the Ordered result Set of a Query

Question

I have a very simple requirement. ``` SELECT NULL as ProjectId, 'All' as ProjectName UNION ( SELECT Project.ProjectId, Project.ProjectName FROM Project Order by 2 ) ``` Original order of entries: ``` ProjectId ProjectName 24 Beta 56 Alpha 57 Gamma 120 Aap ``` EXPECTED Result SET: ``` ProjectId ProjectName ______________________________ NULL All 120 Aap 56 Alpha 24 Beta 57 Gamma ``` What I Need: I want to add a single row on top of the ordered result set of a query Problems: - Subquerys are not allowed to have Order By clause - Doing Top 100 Percent destroys the order, and row having 'All' doesn't come at top 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 help me please

Accepted Answer

You can order result of UNION, but you need to provide additional column to identify where the data came from - here it is 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
```

Note I replaced UNION with UNION ALL as you do not need to DISTINCT result set.

Add a Top Single Row to the Ordered result Set of a Query

Solution

Problem