Last Updated:

SQL : (keyword) ORDER BY Clause in a SELECT Statement (Transact-SQL)

SQL ORDER BY keyword

Configuring the Database for the Samples

Create a database to demonstrate how the commands work. To work, you will need to download two files: DLL.sql and InsertStatements.sql. After that, open a terminal and log in to the MySQL console with the following command (the article assumes that MySQL is already installed on the system):

mysql -u root -p

Then enter your password.

 

Run the following command. Let's call the database "university":

CREATE DATABASE university;
USE university;
SOURCE <p>;
SOURCE<p>;< _istranslated="1" p=""></p>;<></p>;

1. View available databases

SHOW DATABASES;

2. Create a new database

CREATE DATABASE;

3. Choosing the database to use

USE<database_name>; <=""></database_name>;>

4. Import SQL commands from a .sql file

SOURCE<p>; p="" <=""></p>;>

5. Delete the database

DROP DATABASE<database_name>; <=""></database_name>;>

ORDER BY partition

The phrase ORDER BY is used to arrange the rows retrieved by the query.

You can specify multiple expressions in the ORDER BY SQL clause. First, the strings are sorted based on their values for the first expression. Strings with the same value for the first expression are then sorted by the second expression, and so on. Null values are placed after all others when ordered in ascending order and before all others when sorted in descending order.

 

Instead of specifying a column name, you can specify its position to shorten the entry of a long expression.

In addition, when composing complex queries that contain multiple UNION, INTERSECT, MINUS, or UNION ALL operators, it is better to use positions in the ORDER BY clause than the expressions themselves. The ORDER BY clause can appear only in the last component of the query and sorts the rows received by the entire composite query as a whole.

The ORDER BY clause is subject to the following constraints:

  • If both the ORDER BY statement and the DISTINCT operator are used in the SELECT statement, the ORDER BY clause cannot reference columns that are not mentioned in the selector list.
  • The ORDER BY clause cannot appear in subqueries within other statements.

ORDER BY in reverse order

Select from the EMP records for all sellers, and order the results by commission amounts in reverse order (descending order):

SELECT *FROM emp WHERE job = ‘SALESMAN’ ORDER BY comm DESC;

ORDER BY in ascending order

Choose from EMP records for all employees, and order the results by commission amount in ascending order:

SELECT * FROM emp WHERE job = ‘SALESMAN’ ORDER BY comm ASC;

ORDER BY in ascending and descending order

Select from the EMP records for employees, ordered first by increasing department number and then by descending salary:

SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

SQL ORDER BY ключевое слово

 

The ORDER BY keyword is used to sort the result set in ascending or descending order.

The default ORDER BY keyword sorts records in ascending order. To sort the entries in descending order, use the DESC keyword.

Arguments

order_by_expression
Specifies the column or expression by which to sort the query result set. A sort column can be specified by using a column name or alias, or a non-negative integer that represents the column's position in the choice list.

You can specify multiple sort columns. Column names must be unique. The sequence of sort columns in the ORDER BY clause determines the organization of the ordered result set. In other words, the result set is sorted by the first column, then the ordered list is sorted by the second, and so on.

 

The names of the columns referenced in the ORDER BY clause must unambiguously match the column or alias of the column in the choice list, or the column defined in the table specified in the FROM clause. If the ORDER BY clause references a column alias in a choice list, the alias must be used separately and not as part of an expression in the ORDER BY clause, for example:

SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects ORDER BY SchemaName; — correct SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects ORDER BY SchemaName + »; — wrong

COLLATE collation_name
Specifies that the ORDER BY operation should be performed according to the collation specified in the collation_name argument, but not according to the column collation defined in the table or view. The collation_name argument can be either the Windows collation name or the SQL collation name. For more information, see Collation and Unicode Support. The COLLATE argument applies only to char, varchar, nchar, and nvarchar columns.

ASC | DESC
Specifies whether the values in the specified column are sorted in ascending or descending order. The ASC value sorts from low to high. The DESC value sorts from high to low. The default sort order is ASC. NULL values are treated as the lowest possible values.

 

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifies the number of drains to skip before returning strings from a query expression. This value can be an integer constant or an expression whose value is greater than zero or equal to zero.

Applies to: SQL Server 2012 (11.x) and above, Azure SQL Database.

offset_row_count_expression can be a variable, a parameter, or a nested query that returns a scalar constant. When you use a subquery, it should not reference any columns that are defined in the scope of the external query. In other words, it cannot correlate with an external query.

ROW and ROWS are synonymous and reserved for ansi standard compatibility.

In the query execution plan, the value of the row offset is displayed in the Offset attribute of the TOP query operator.

 

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
Specifies the number of rows returned after the OFFSET clause is processed. This value can be an integer constant or an expression whose value is greater than one or equal to one.

Applies to: SQL Server 2012 (11.x) and above, and Azure SQL Database.

 

fetch_row_count_expression can be a variable, a parameter, or a nested query that returns a scalar constant. When you use a subquery, it should not reference any columns that are defined in the scope of the external query. In other words, it cannot correlate with an external query.

FIRST and NEXT are synonymous and are designed to be compatible with the ANSI standard.

ROW and ROWS are synonymous and reserved for ansi standard compatibility.

In the query execution plan, the value of the row offset is displayed in the Rows attribute or the Top attribute of the TOP query operator.

Recommendations

Avoid specifying columns in the ORDER BY clause by their ordinal number in the choice list. For example, although the SELECT ProductID, Name FROM Production.Production ORDER BY 2 statement is correct, it will not be very clear to other users compared to the case when the columns are named. In addition, if the selection list changes, such as changing the order of the column, or adding new columns, this will require modifying the ORDER BY clause to avoid unexpected results.

In the SELECT TOP (N) statement, always specify the ORDER BY clause. This is the only way to predictably mark the lines that have been processed by the TOP clause. For more information, see TOP (Transact-SQL).

Compatibility

When used in a SELECT statement... INTO or INSERT... SELECT the ORDER BY clause for inserting rows from another source, inserting rows in the specified order is not guaranteed.

Using OFFSET and FETCH in a view will not change its Updateability property.

Restrictions

There is no limit to the number of columns in the ORDER BY clause, but the total size of the columns listed in the clause cannot exceed 8060 bytes.

Columns of type ntext, text, image, geography, geometry, and xml cannot be used in an ORDER BY clause.

 
 

You cannot specify an integer or constant if the order_by_expression argument is present in the ranking function. For more information, see OVER Clause (Transact-SQL).

If you use an alias as the table name in the FROM clause, only an alias can be used to identify the column of that table in the ORDER BY clause.

The column names and aliases specified in the ORDER BY clause must be defined in the choice list if the SELECT statement contains one of the following clauses or operators:

  • UNION, оператор

  • Оператор EXCEPT

  • INTERSECT, operator

  • SELECT DISTINCT

In addition, if the statement includes the UNION, EXCEPT, or INTERSECT operator, the column names and aliases must be listed in the selection list for the first (left) query.

In a query that contains a UNION, EXCEPT, or INTERSECT operator, the ORDER BY clause is allowed only at the end of the statement. This limit applies only when you use the UNION, EXCEPT, and INTERSECT operators in top-level queries, not in nested queries. See the "Examples" section below.

The ORDER BY clause is not valid in views, embedded functions, derived tables, and nested queries unless you also specify top or OFFSET and FETCH clauses. In these objects, the ORDER BY clause is used only to define the strings returned by the TOP or OFFSET and FETCH clause. The ORDER BY clause does not guarantee that the results will be ordered when these constructs are queried unless it is specified in the query itself.

 

The OFFSET and FETCH clauses are not supported in indexed views and views defined with the CHECK OPTION clause.

The OFFSET and FETCH clauses can be used in any query that allows TOP and ORDER BY to be used, with the following restrictions:

  • The OVER clause does not support OFFSET and FETCH.

  • The OFFSET and FETCH clauses cannot be specified directly in INSERT, UPDATE, MERGE, and DELETE statements, but can be specified in the nested queries defined by those statements. For example, in an INSERT INTO SELECT statement, the OFFSET and FETCH clauses can be specified in a SELECT statement.

  • In a query that contains a UNION, EXCEPT, or INTERSECT operator, the OFFSET and FETCH clauses can be specified only in the final query, which determines the order in which the query results are in order.

  • TOP cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope).

Using OFFSET and FETCH to limit the number of rows returned

To paginate and limit the number of rows passed to the client application, we recommend that you use the OFFSET and FETCH clauses rather than the TOP clause.

Using the OFFSET and FETCH clauses as a pagination solution will require a one-time query execution for each "page" of data returned to the client application. For example, to return query results in blocks of 10 rows, you would run the query to get rows 1 through 10, then again to retrieve rows 11 through 20, and so on. Each request is executed independently and is in no way related to other requests. This means that unlike using the cursor, where the request is executed only once and the current state is stored on the server, the client application is responsible for monitoring the state. To achieve consistent results between OFFSET and FETCH RFQs, the following conditions must be met:

  1. The underlying data used by the query must be unchanged. In other words, either the strings processed by the request should not be updated, or all requests to the pages of the query in progress should be executed in a single transaction that uses a snapshot or serializable isolation of the transaction. For more information about transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  2. An ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

Cm. The example "Executing multiple queries in a single transaction" is in the "Examples" section later in this topic.

If consistency in execution plans is important to the pagination solution, consider specifying OPTIMIZE FOR queries for the OFFSET and FETCH parameters. Cm. "Specifying expressions for OFFSET and FETCH values" in the "Examples" section later in this topic. For more information about OPTIMZE FOR, see Specify Queries (Transact-SQL).

Demo database

The following is a selection from the Customers table in the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

Пример ORDER BY

The following SQL statement selects all customers from the Customers table, sorted by the Country column:

Example

SELECT * FROM Customers
ORDER BY Country;

Пример ORDER BY DESC

The following SQL statement selects all customers from the Customers table, sorted in descending order of the Country column:

Example

SELECT * FROM Customers
ORDER BY Country DESC;

Example of ORDER BY multiple columns

The following SQL statement selects all customers from the Customers table, sorted by country and CustomerName columns. This means that it orders by country, but if some lines have the same country, it orders them by the customer's name:

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;

Grouping

  • GROUP BY
  • group by HAVING

String addition

  • concat string
    addition
  • addition with CONCAT_WS separator
  • addition of cells
    GROUP_CONCAT

String length

  • LENGTH string
    length

Edit strings

  • left left cut
  • right right cut
  • taking the
    SUBSTRING substring
  • taking the MID substring
  • taking a substring
    SUBSTRING_INDEX
  • replace and find and replace
  • addition of the line on the left
    LPAD
  • row addition on the right
    RPAD
  • REVERSE STRING
    FLIP
  • repeating the repeat line
    REPEAT

Search by strings

  • find the POSITION substring
  • locate substring
    search
  • find the INSTR substring

Work with spaces


  • trim TRIM spaces
  • cropping spaces on the left
    LTRIM
  • cropping spaces on the right
    RTRIM
  • space string
    SPACE

Working with the register

  • Lowercase
    LCASE, LOWER
  • upper case
    UCASE, UPPER