如何在sql中使用order by和union all ?

I tried the sql query given below:

我尝试了下面给出的sql查询:

SELECT * FROM (SELECT * 
FROM TABLE_A ORDER BY COLUMN_1)DUMMY_TABLE
UNION ALL 
SELECT * FROM TABLE_B

It results in the following error:

它导致以下错误:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

ORDER BY子句在视图、内联函数、派生表、子查询和通用表表达式中无效，除非TOP或XML也被指定。

I need to use order by in union all. How do I accomplish this?

我需要在联盟中使用秩序。我如何做到这一点?

4 个解决方案

#1

SELECT  * 
FROM 
        (
            SELECT * FROM TABLE_A 
            UNION ALL 
            SELECT * FROM TABLE_B
        ) dum
-- ORDER BY .....

but if you want to have all records from Table_A on the top of the result list, the you can add user define value which you can use for ordering,

但是如果你想要所有来自Table_A的记录在结果列表的顶部，你可以添加用户定义值你可以用来排序，

SELECT  * 
FROM 
        (
            SELECT *, 1 sortby FROM TABLE_A 
            UNION ALL 
            SELECT *, 2 sortby FROM TABLE_B
        ) dum
ORDER   BY sortby

#2

You don't really need to have parenthesis. You can sort directly:

不需要括号。你可以直接:

SELECT *, 1 AS RN FROM TABLE_A
UNION ALL 
SELECT *, 2 AS RN FROM TABLE_B
ORDER BY RN, COLUMN_1

#3

Not an OP direct response, but I thought I would jimmy in here responding to the the OP's ERROR messsage, which may point you in another direction entirely!

不是OP的直接反应，但是我想我应该在这里对OP的错误信息做出回应，这可能会把你引向另一个完全不同的方向!

All these answers are referring to an overall ORDER BY once the record set has been retrieved and you sort the lot.

所有这些答案都指的是一个整体的顺序，一旦记录集被检索到，你排序了很多。

What if you want to ORDER BY each portion of the UNION independantly, and still have them "joined" in the same SELECT?

如果您希望按联盟的每个部分独立排序，并且仍然让它们在相同的选择中“加入”，该怎么办?

SELECT pass1.* FROM 
 (SELECT TOP 1000 tblA.ID, tblA.CustomerName 
  FROM TABLE_A AS tblA ORDER BY 2) AS pass1
UNION ALL 
SELECT pass2.* FROM 
  (SELECT TOP 1000 tblB.ID, tblB.CustomerName 
   FROM TABLE_B AS tblB ORDER BY 2) AS pass2

Note the TOP 1000 is an arbitary number. Use a big enough number to capture all of the data you require.

注意，前1000是一个任意数。使用足够大的数字来捕获所需的所有数据。

#4

-5

Select 'Shambhu' as ShambhuNewsFeed,Note as [News Fedd],NotificationId
from Notification with(nolock) where DesignationId=@Designation 
Union All 
Select 'Shambhu' as ShambhuNewsFeed,Note as [Notification],NotificationId
from Notification with(nolock) 
where DesignationId=@Designation 
order by NotificationId desc

#1

SELECT  * 
FROM 
        (
            SELECT * FROM TABLE_A 
            UNION ALL 
            SELECT * FROM TABLE_B
        ) dum
-- ORDER BY .....