Multiple ways to sequence rows in a TSQL ORDER BY clause

The ORDER BY clause is used to sort the results, followed by the SELECT clause, with 4 of the sequence:

• column_name

• Column_alias, because the ORDER BY clause is executed after the SELECT clause, the alias of the column can be used;

• nonnegative integer that represents the position of column in the SELECT clause, starting at 1;

• Column Expression

You can also specify multi-column sorting, with each column appearing only once, sorted by the first column, followed by the second, sorted by the third column, and so on.

1. Create Sample table Data

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

CREATE TABLE [dbo]. [Dt_test] ([ID] [int] NULL, [code] [int] null, [name] [varchar] (TEN) null)

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

2, using column name

Select Id,code,name from Dbo.dt_test DT with (NOLOCK) Order by dt.id

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201510/628084-20151015182407179-1893246768. JPG "style=" margin:0px;padding:0px;border:0px; "/>

3, the reason that you can use Alias in the column Alias,order by clause is that SQL Server engine executes the SELECT clause before executing the ORDER BY clause.

Select ID as orderid,code,name from dbo.dt_test DT with (NOLOCK) Order by OrderID

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201510/628084-20151015182512757-836194158. JPG "style=" margin:0px;padding:0px;border:0px; "/>

4, use column order to sort, even if a positive integer is used to table the Order of column in SELECT clause

Select ID, code,name from dbo.dt_test DT with (NOLOCK) Order by 1

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201510/628084-20151015182407179-1893246768. JPG "style=" margin:0px;padding:0px;border:0px; "/>

5, using column expression to sort, the order of execution is to calculate the value of column Expressino first, and then to sort the results of the query.

Select ID, code,name from dbo.dt_test DT with (NOLOCK) Order by Id+code

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201510/628084-20151015182807397-2736934.jpg "Style=" margin:0px;padding:0px;border:0px; "/>

6, using column expression to sort, the order of execution is to calculate the value of column Expressino first, and then to sort the results of the query.

Select ID, code,name from dbo.dt_test DT with (NOLOCK) Order by id%3

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201510/628084-20151015183100601-1642605512. JPG "style=" margin:0px;padding:0px;border:0px; "/>

Multiple ways to sequence rows in a TSQL ORDER BY clause