Sorting records in the table (Summary of order by usage in SQL Server)

RelatedArticleNavigation

1. SQL server2005 Transact-SQL new weapon learning Summary-Summary
2. Index of flex and fms3 articles
3. Flexair open-source edition-global free multi-person video chat room, free network remote multi-person Video Conferencing System (jointly developed by flex and fms3) <video chat, conference Development Instance 8>

When dealing with databases, queries are the most used. Since there is a query record Sorting Problem in queries, I have been using order by to solve this problem. I will not mention the commonly used order by methods.

The demand for projects is ever-changing. Let's look at the following strange sorting requirements:

-- Create a table first
Create Table AI (
Id int not null,
No varchar (10) Not null
)
Go

-- Insert data into the table
Insert into ai
Select 105, '2'
Union all
Select 105, '1'
Union all
Select 103, '1'
Union all
Select 105, '4'
Go

-- The query result is as follows:
Select * from AI
Go
ID No
---------------------
105 2
105 1
103 1
105 4

I.
-- The query result is as follows:
-- Requires that data in the no column be arranged by '4', '1', and '2'
ID No
---------------------
105 4
105 1
103 1
105 2

-- Solution 1
-- Use the charindex Function
Select * from AI
Order by charindex (No, '4, 1, 2 ')

-- Solution 2
-- Use function case
Select * from AI
Order by case when no = '4' then 1
When no = '1' then 2
When no = '2' then 3
End

-- Solution 3
-- Use the Union operator
Select * from AI
Where no = '4'
Union all
Select * from AI
Where no = '1'
Union all
Select * from AI
Where no = '2'

II.
-- You must specify no = '4' for the first row in the query. Other rows are sorted randomly.
ID No
---------------------
105 4
105 2
105 1
103 1

-- Solution
Select * from AI
Order by case when no = '4' then 1
Else 1 + rand ()
End

III.
-- The query requires random sorting of all rows

-- Solution
Select * from AI
Order by newid ()

Iiii
-- A Table AB has column I, and the data is as follows:
I varchar (10)
A1
A10
A101
A5
P4
P41
P5

-- The data in column I must be sorted by letters and then by numbers.
-- The effect is as follows:
A1
A5
A10
A101
P4
P5
P41

-- Solution
Select * from AB
Order by left (I, 1), convert (INT, substring (I, 2,8000 ))

 

I hope the Knowledge mentioned above will prompt you.

Of course, you are welcome to contact us and correct us.

 

 

 

Favorites and sharing

Add QQ bookmarks to Baidu souzang {
Function onclick ()
{
Window. Open ('HTTP: // myweb.cn.yahoo.com/popadd.html? Url = '+ encodeuricomponent (document. location. href) + '& Title =' + encodeuricomponent (document. title), 'yahoo ', 'scrollbars = Yes, width = 440, Height = 440, Left = 80, Top = 80, status = Yes, resizable = Yes ');
}
} "> Add to Yahoo favorites

RSS subscribe to me What is RSS?

Dongguan. Net Club

Welcome to join