Order by cannot be directly used in the union clause.

Source: Internet
Author: User
Tags table definition
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 ()


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
) 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
(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
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.