Today say order by this regular thing ~

Order by in our daily database development life is the mirror rate of gray often high.

The order by function is used to sort the results of the query ~ Yes, it is so grounded gas ~ For example, according to the time Ah, the first letter ah and so on are quite common.

Today I would like to share the usage and examples of order by.

General usage

CREATE TABLE#Tmp1 (IDINT IDENTITY, Col1NVARCHAR( -), Col2NVARCHAR(Max))INSERT  into#Tmp1 (Col1, Col2)VALUES(N'AAA',REPLICATE('AAA', the)), (N'AAA',REPLICATE('AAA', the)), (N'AAA',REPLICATE('AAA', the)), (N'AaA',REPLICATE('AaA', the ))            SELECT *     from#Tmp1ORDER  byId

This is the simplest of all ~ and then if we want an odd number first, even the rear--this will use the expression skills in order by

SELECT *     from #Tmp1     ORDER  by  Case  when ID%2>0then1ELSE2 END, ID

Support Expression ~ Whether you want to sum or product in order BY, all can satisfy your request ~ But one thing is that the data type is consistent, at least it can be implicitly converted to the uniform ╮ (╯_╰) ╭. Otherwise, 1 and a who big?

And then look at my example ~ Although all AAA, of course, the case is not the same, but usually in our default sort, the case is not distinguished, A and a is a person. If you want to add the case sort ROM, then it's good to refer to the sort sequence after the order by

Sorting rules Sort reference: https://msdn.microsoft.com/zh-cn/library/ms143726 (v=sql.120). aspx

SELECT *     from #Tmp1         ORDER  by ASC

However, there are some types that cannot be sorted, such as Ntext,text, image, XML, and geography types, which cannot be sorted by using order by.

However, in the version after 2012, SQL Server provides a convenient query for paging statement Offset FETCH statements (However, it seems like MySQL has supported this type of writing in the early morning, but performance is not the same)

When we take the first 2 lines of writing, it's a lot easier to develop than to write a bunch of paging statements.

SELECT *     from #Tmp1         ORDER  by ASC  0 ROWS FETCH NEXT2only                  

There are 2 points to note

1, offset starting from 0, not 1

2. If offset is more than the total number of rows returned, no rows will be returned

Thank you again to see me the seriousness of the nonsense ~ Wish you all Happy New Year

Today say order by this regular thing ~