Several Writing methods of arranging sequence in TSQL order by clause

The Order by clause is used to sort the results, the order of execution is after the select clause, and the sort column is written in four ways:

Column_name

Column_alias, because the execution order of the order by clause is after the select clause, you can use the alias of the column

Nonnegative integer, which is used to indicate the position of column in the select clause, starting with 1

Column Expression

You can also specify multi-column sorting, each column can only appear once, the sorting rule is first sorted by the first column, then sorted by the second column, then sorted by the third column, and so on.

1, create sample table data

CREATE TABLE [dbo]. [dt_test] ([id] [int] NULL, [code] [int] NULL, [name] [varchar] (10) NULL)

2, use column name

Select id,code,name from dbo.dt_test dt with (nolock) order by dt.id

3. The reason why Alias can be used in the Column Alias,order by clause is that SQL Server Engine executes the select clause first, followed by the order by clause.

Select id as OrderID,code,name from dbo.dt_test dt with (nolock) order by OrderID

4. Use column order to sort, even if a positive integer is used to list the order of column in Select clause

Select id, code,name from dbo.dt_test dt with (nolock) order by 1

5. Use column expression to sort, in the order that the value of Column Expressino is calculated first, and then the results of the query are sorted.

Select id, code,name from dbo.dt_test dt with (nolock) order by id+code

6. Use column expression to sort, in the order that the value of Column Expressino is calculated first, and then the results of the query are sorted.

Select id, code,name from dbo.dt_test dt with (nolock) order by id%3