What is ORDER BY clause in SQL with example?

Question

Let us explore more on this topic with the help of examples. We will use the MySQL database for writing the queries in examples.

Nội dung chính Show

Introduction to the SQL Server ORDER BY clause
SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)3 | SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)4
SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)7 | SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)8
SQL Server ORDER BY clause example
A) Sort a result set by one column in ascending order
B) Sort a result set by one column in descending order
C) Sort a result set by multiple columns
D) Sort a result set by multiple columns and different orders
E) Sort a result set by a column that is not in the select list
F) Sort a result set by an expression
G) Sort by ordinal positions of columns
What is ORDER BY clause?
What is ORDER BY 4 in SQL?
What is the use of ORDER BY 1 clause?
What is ORDER BY 3 in SQL?

Consider we have customers table with the following records:

IDNAMEAGEADDRESSSALARY2Shiva Tiwari22Bhopal210003Ajeet Bhargav45Meerut650004Ritesh Yadav36Azamgarh260005Balwant Singh45Varanasi360006Mahesh Sharma26Mathura220007Rohit Shrivastav19Ahemdabad380008Neeru Sharma29Pune400009Aakash Yadav32Mumbai4350010Sahil Sheikh35Aurangabad68800

Example 1:

Write a query to sort the records in the ascending order of the customer names stored in the customers table.

Query:

Here in a SELECT query, an ORDER BY clause is applied on the column 'Name' to sort the records. ASC keyword will sort the records in ascending order.

You will get the following output:

IDNAMEAGEADDRESSSALARY9Aakash Yadav32Mumbai435003Ajeet Bhargav45Meerut650005Balwant Singh45Varanasi360001Himani Gupta21Modinagar220006Mahesh Sharma26Mathura220008Neeru Sharma29Pune400004Ritesh Yadav36Azamgarh260007Rohit Shrivastav19Ahemdabad3800010Sahil Sheikh35Aurangabad688002Shiva Tiwari22Bhopal21000

All the records present in the agents table are displayed in the descending order of the customer's address.

Summary: in this tutorial, you will learn how to use the SQL Server ORDER BY clause to sort the result set of a query by one or more columns.

Introduction to the SQL Server ORDER BY clause

When you use the

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

0 statement to query data from a table, the order of rows in the result set is not guaranteed. It means that SQL Server can return a result set with an unspecified order of rows.

The only way for you to guarantee that the rows in the result set are sorted is to use the ORDER BY clause. The following illustrates the ORDER BY clause syntax:

SELECT
    select_list
FROM
    table_name
ORDER BY 
    column_name | expression [ASC | DESC ];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)3 | SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)4

First, you specify a column name or an expression on which to sort the result set of the query. If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on.

The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

6 clause.

SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)7 | SELECT first_name, last_name FROM sales.customers ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)8

Second, use

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 or

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

8 to specify whether the values in the specified column should be sorted in ascending or descending order.

The

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 sorts the result from the lowest value to the highest value while the

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

8 sorts the result set from the highest value to the lowest one.

If you don’t explicitly specify

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 or

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

8, SQL Server uses

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 as the default sort order. Also, SQL Server treats NULL as the lowest value.

When processing the

SELECT
	firstname,
	lastname
FROM
	sales.customers
ORDER BY
	first_name DESC;
Code language: SQL (Structured Query Language) (sql)

6 statement that has an ORDER BY clause, the ORDER BY clause is the very last clause to be processed.

SQL Server ORDER BY clause example

We will use the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

0 table in the sample database from the demonstration.

A) Sort a result set by one column in ascending order

The following statement sorts the customer list by the first name in ascending order:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

In this example, because we did not specify

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 or

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

8, the ORDER BY clause used

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 by default.

B) Sort a result set by one column in descending order

The following statement sorts the customer list by the first name in descending order.

SELECT
	firstname,
	lastname
FROM
	sales.customers
ORDER BY
	first_name DESC;
Code language: SQL (Structured Query Language) (sql)

In this example, because we specified the

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)

8 explicitly, the ORDER BY clause sorted the result set by values in the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 column in descending order.

C) Sort a result set by multiple columns

The following statement retrieves the first name, last name, and city of the customers. It sorts the customer list by the city first and then by the first name.

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

D) Sort a result set by multiple columns and different orders

The following statement sorts the customers by the city in descending order and then sorts the sorted result set by the first name in ascending order.

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city DESC,
    first_name ASC;
Code language: SQL (Structured Query Language) (sql)

E) Sort a result set by a column that is not in the select list

It is possible to sort the result set by a column that does not appear on the select list. For example, the following statement sorts the customer by the state even though the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

8 column does not appear on the select list.

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    state;
Code language: SQL (Structured Query Language) (sql)

Note that the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

8 column is defined in the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

0 table. If it was not, then you would have an invalid query.

F) Sort a result set by an expression

The

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city DESC,
    first_name ASC;
Code language: SQL (Structured Query Language) (sql)

1 function returns the number of characters in a string. The following statement uses the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city DESC,
    first_name ASC;
Code language: SQL (Structured Query Language) (sql)

1 function in the ORDER BY clause to retrieve a customer list sorted by the length of the first name:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    LEN(first_name) DESC;
Code language: SQL (Structured Query Language) (sql)

G) Sort by ordinal positions of columns

SQL Server allows you to sort the result set based on the ordinal positions of columns that appear in the select list.

The following statement sorts the customers by first name and last name. But instead of specifying the column names explicitly, it uses the ordinal positions of the columns:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    1,
    2;
Code language: SQL (Structured Query Language) (sql)

In this example, 1 means the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;
Code language: SQL (Structured Query Language) (sql)

7 column, and 2 means the

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city DESC,
    first_name ASC;
Code language: SQL (Structured Query Language) (sql)

5 column.

Using the ordinal positions of columns in the ORDER BY clause is considered a bad programming practice for a couple of reasons.

First, the columns in a table don’t have ordinal positions and need to be referenced by name.
Second, when you modify the select list, you may forget to make the corresponding changes in the ORDER BY clause.

Therefore, it is a good practice to always specify the column names explicitly in the ORDER BY clause.

In this tutorial, you have learned how to use the SQL Server ORDER BY clause to sort a result set by columns in ascending or descending order.

What is ORDER BY clause?

An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set.

What is ORDER BY 4 in SQL?

You also may notice that the number 4 is specified in the order by clause. The number 4 specifies the position of the columns in the SQL query. In this case, position of BusinessEntityID is 1, FirstName is 2, MiddleName is 3 and LastName is 4.

What is the use of ORDER BY 1 clause?

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

What is ORDER BY 3 in SQL?

Order by 3 DESC. In this query, column birthdate is at the 3rd position; therefore, we can use three in the Order by clause to sort results on this column data. Note: I would not recommend using column position in Order By clause. You should always use a column name in Order by clause.

SQL ORDER BY