Sort by in MS SQL Server

Order by is a clause in SQL. It is used to sort the result set of a query in ascending or descending order. It can be sorted using one or more columns. In this article, we will discuss Order by clause in MS SQL Server.

grammar

The syntax for using the Order by clause in MS SQL Server is as follows -

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Glossary explanation

• SELECT column1, column2, ...: Specify the columns we want to retrieve from the table.

• FROM table_name: Specifies the name of the table from which we want to retrieve data.

• ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...: Sort the result set in ascending or descending order according to the specified column.

Ascending sort

By default, the Order by clause sorts the result set in ascending order. To sort the result set in ascending order, we do not need to specify the ASC keyword explicitly.

Example 1

SELECT * FROM customers
ORDER BY customer_name;

In this example, the result set will be sorted in ascending order based on the customer_name column.

Example 2

Suppose we have a table named "Employees" with the following columns and data -

Employee ID	employee's name	department	Salary
1	John	IT	50000
2	simple	Sale	45000
3	Bob	IT	55000
4	Alice	human Resources	40000
5	Tom	human Resources	42000

If we want to retrieve all data from Employees table and sort by Salary column in ascending order, the SQL query will be -

SELECT * FROM Employees
ORDER BY Salary;

The output of the above query is -

Employee ID	employee's name	department	Salary
4	Alice	human Resources	40000
5	Tom	human Resources	42000
2	simple	Sale	45000
1	John	IT	50000
3	Bob	IT	55000

Sort in descending order

To sort the result set in descending order, we need to specify the DESC keyword after the column name.

Example 1

SELECT * FROM customers
ORDER BY customer_name DESC;

In this example, the result set will be sorted in descending order based on the customer_name column.

Example 2

If we wanted to retrieve all data from the employees table and sort by department column in descending order, the SQL query would be -

SELECT * FROM Employees
ORDER BY Department DESC;

The output of the above query is

Employee ID	employee's name	department	Salary
2	simple	Sale	45000
1	John	IT	50000
3	Bob	IT	55000
5	Tom	human Resources	42000
4	Alice	human Resources	40000

按多列排序

我们还可以按多列对结果集进行排序。在本例中，Order by 子句首先根据第一列对结果集进行排序，然后根据第二列对结果集进行排序。

示例 1

SELECT * FROM customers
ORDER BY country, customer_name;

在此示例中，结果集将根据国家/地区列按升序排序。如果两行或多行具有相同的国家/地区，则这些行将根据 customer_name 列按升序排序。

示例 2

如果我们想从Employees表中检索所有数据，并首先按Department列升序排序，然后按Salary列升序排序，SQL查询将是 -

SELECT * FROM Employees
ORDER BY Department DESC;

上述查询的输出为

员工ID	员工姓名	部门	薪资
2	简	销售	45000
1	约翰	IT	50000
3	鲍勃	IT	55000
5	汤姆	人力资源	42000
4	爱丽丝	人力资源	40000

按多列排序

我们还可以按多列对结果集进行排序。在本例中，Order by 子句首先根据第一列对结果集进行排序，然后根据第二列对结果集进行排序。

示例 1

SELECT * FROM customers
ORDER BY country, customer_name;

在此示例中，结果集将根据国家/地区列按升序排序。如果两行或多行具有相同的国家/地区，则这些行将根据 customer_name 列按升序排序。

示例 2

如果我们想要从Employees表中检索所有数据，并首先按Department列升序排序，然后按Salary列升序排序，那么SQL查询将是

SELECT * FROM Employees
ORDER BY Department ASC, Salary ASC;

上述查询的输出为

员工ID	员工姓名	部门	薪资
4	爱丽丝	人力资源	40000
5	汤姆	人力资源	42000
1	约翰	IT	50000
3	鲍勃	IT	55000
2	简	销售	45000

这些示例演示了如何使用 Order by 子句对 MS SQL Server 中的查询结果集进行排序。

使用 NULL 值排序

当我们使用 Order by 子句时，NULL 值的排序方式有所不同，具体取决于我们是按升序还是降序排序。按升序排列，首先显示 NULL 值，按降序排列，最后显示 NULL 值。

例如

SELECT * FROM customers
ORDER BY city DESC;

在此示例中，结果集将根据城市列按降序排序。 NULL 值将显示在最后。

结论

MS SQL Server 中的 Order by 子句是一个功能强大的工具，它允许我们根据一个或多个列以升序或降序对查询结果集进行排序。通过了解 Order by 子句的语法和用法，我们可以创建更复杂且更有意义的查询来从数据库中检索数据。

The above is the detailed content of Sort by in MS SQL Server. For more information, please follow other related articles on the PHP Chinese website!