在Sql Server中使用Union的order by子句

I want List of party names with 1st option as 'All' from database. but i won't insert 'All' to Database, needs only retrieve time. so, I wrote this query.

我希望第一个选项的名单列表为数据库中的“全部”。但我不会将“全部”插入数据库，只需要检索时间。所以，我写了这个查询。

Select 0 PartyId, 'All' Name
Union
select PartyId, Name
from PartyMst

This is my Result

这是我的结果

0   All
1   SHIV ELECTRONICS
2   AAKASH & CO.
3   SHAH & CO.

when I use order by Name it displays below result.

当我按名称使用订单时，它显示在结果下面。

2   AAKASH & CO.
0   All
3   SHAH & CO.
1   SHIV ELECTRONICS

But, I want 1st Option as 'All' and then list of Parties in Sorted order. How can I do this?

但是，我希望第一个选项为“全部”，然后按排序顺序列出各方。我怎样才能做到这一点？

3 个解决方案

#1

You need to use a sub-query with CASE in ORDER BY clause like this:

您需要在ORDER BY子句中使用带CASE的子查询，如下所示：

SELECT * FROM
(
  Select 0 PartyId, 'All' Name
  Union
  select PartyId, Name
  from PartyMst
) tbl
ORDER BY CASE WHEN PartyId = 0 THEN 0 ELSE 1 END
,Name

Output:

输出：

| PARTYID |             NAME |
------------------------------
|       0 |              All |
|       2 |     AAKASH & CO. |
|       3 |       SHAH & CO. |
|       1 | SHIV ELECTRONICS |

See this SQLFiddle

#2

Since you are anyway hardcoding 0, All just add a space before the All

因为你无论如何硬编码0，所以只需在All之前添加一个空格

Select 0 PartyId, ' All' Name
Union
select PartyId, Name
from PartyMst
ORDER BY Name

SQL FIDDLE

SQL FIDDLE

Raj

拉吉

#3

You can use 'order by' with the top instrucction in a union in this way:

您可以通过以下方式使用联合中的*指令来“使用”：

Select 0 PartyId, 'All' Name
Union
select * from ( 
  select top (select count(*) from PartyMst) PartyId, Name
  from PartyMst
  order by Name
)