The ORDER BY operator in SQL Server is used to sort the results of a query in ascending or descending order based on one or more columns. It is used in SELECT statements to specify the order in which the returned data should be sorted.

Syntax

The basic syntax for the ORDER BY clause is as follows:

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

Here, column1, column2, etc. are the names of the columns to be sorted, and table_name is the name of the table from which data is being retrieved. The ASC keyword is used to sort the data in ascending order, and the DESC keyword is used to sort the data in descending order.

Example

If you specify more than one column in the ORDER BY clause, the results will be sorted by the first column, and then by the second column, and so on. For example, the following query sorts the results of a SELECT statement by the LastName column in ascending order, and then by the FirstName column in descending order:

SELECT FirstName, LastName
FROM Customers
ORDER BY LastName ASC, FirstName DESC;

You can also use expressions in the ORDER BY clause to sort the data based on computed values. For example, the following query sorts the results of a SELECT statement by the sum of the UnitsInStock and UnitsOnOrder columns in descending order:

SELECT ProductName, UnitsInStock, UnitsOnOrder
FROM Products
ORDER BY UnitsInStock + UnitsOnOrder DESC;

In addition, you can use the ORDER BY clause with the TOP clause to limit the number of rows returned by a query. For example, the following query returns the top 10 rows from the Employees table, sorted by the LastName column in ascending order:

SELECT TOP 10 FirstName, LastName
FROM Employees
ORDER BY LastName ASC;

Example 2

SELECT c.customer_id, c.amount
   FROM contracts c
  WHERE c.amount < 2500
GROUP BY c.customer_id, c.amount
HAVING MIN(c.amount) > 1000
ORDER BY c.amount ;

SELECT c.customer_id, c.amount
   FROM contracts c
  WHERE c.amount < 2500
GROUP BY c.customer_id, c.amount
HAVING MIN(c.amount) > 1000
ORDER BY c.amount DESC;

In summary, the ORDER BY operator is an essential tool for sorting and organizing the results of SQL queries. By using this operator, you can control the order in which data is presented to users and make it easier to analyze and understand.