Order by cannot be directly used in the union clause.

Order by cannot be directly used in the union clause.

Today, a brother in a group asked a question, saying that there is a table with a lot of data in it. There is a field type, and we want to randomly retrieve 10 records from the table, five Types = 1 and five other types = 0, for example:

As I thought, this is not simple. According to the requirements, every top 5 is taken. Since it is a random query, order by newid () is three, and then all the data is union. So I immediately gave the answer:

Select top 5 * from XXX Where type = 1 order by newid ()

Union

Select top 5 * from XXX Where type = 0 order by newid ()

But the brother said no, and the syntax is wrong. At first glance, it seems that there is no problem, so I asked him to send the table definition to me. I will try it on the local machine. The table definition is:

Create Table [DBO]. [question] (
[ID] [int] identity (1, 1) not null,
[Question] [varchar] (50) not null,
[Answer] [varchar] (50) not null,
[Type] [bit] not null,
Constraint [pk_question] primary key clustered
(
[ID] ASC
) With (pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]
) On [primary]

So I entered my statement into SSMs, and the system reported an error: incorrect syntax near the keyword 'order '.

Why? It doesn't look like a problem. So I will execute it separately

Select top 5 * From DBO. Question
Where [type] = 1
Order by newid ()

Yes. No problem. The following half sentence is the same syntax structure and there is no error. However, an error occurred while union the two queries. Strange. Since the prompt is that there is an error near order, I will remove order:

(Select top 5 * From DBO. Question
Where [type] = 1
)
Union
(Select top 5 * From DBO. Question
Where [type] = 0
)

No error. That is to say, the query in the union clause cannot use order. But here I must use order by. Otherwise, how can I retrieve five random records? Can union be used? Yes. Create a temporary table and insert the five queried data records into the table. Then query the temporary table, but try not to use the temporary table without using the temporary table. Why is it so complicated.

What should I do if I want to use order by and union? Order by cannot appear directly in the union clause, but it can appear in the clause of the clause! The query is eventually rewritten:

Select * from
(Select top (5) * From DBO. question where [type] = 1 order by newid () T1
Union
Select * from
(Select top (5) * From DBO. question where [type] = 0 order by newid () T2

It seems awkward, but it cannot be done. I don't know why SQL Server cannot use order by in the union clause, so this is the only option.