MySQL ORDER BY

 MySQL ORDER BY

Summary: in this tutorial, you will learn how to sort a result set using the MySQL ORDER BY clause.

Introduction to MySQL ORDER BY clause

When you use the SELECT statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order.

To sort the result set, you add the ORDER BY clause to the SELECT statement. The following illustrates the syntax of the ORDER BY  clause:

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

In this syntax, you specify the one or more columns that you want to sort after the ORDER BY clause.

The ASC stands for ascending and the DESC stands for descending. You use ASC it to sort the result set in ascending order and DESC to sort the result set in descending order.

This ORDER BY clause sorts the result set in ascending order:

ORDER BY column1 ASC;

And this ORDER BY clause sorts the result set in descending order:

ORDER BY column1 DESC;

By default, the ORDER BY clause uses ASC if you don’t explicitly specify any option.

Therefore, the following clauses are equivalent:

ORDER BY column1 ASC;

and

ORDER BY column1;

If you want to sort the result set by multiple columns, you specify a comma-separated list of columns in the ORDER BY clause:

ORDER BY
   column1,
   column2;

It is possible to sort the result by a column in ascending order, and then by another column in descending order:

ORDER BY
    column1 ASC,
    column2 DESC;

In this case, the ORDER BY clause:

• First, sort the result set by the values in the column1 in ascending order.
• Then, sort the sorted result set by the values in  column2  in descending order. Note that the order of values in the column1 will not change in this step, only the order of values in the column2 changes.

Note that the ORDER BY a clause is always evaluated after the FROM and SELECT clause.

MySQL ORDER BY examples

We’ll use the customers table from the sample database for the demonstration.

A) Using MySQL ORDER BY clause to sort values in one column example

The following query uses the ORDER BY clause to sort the customers by the values in the contactLastName the column in ascending order.

SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname;

If you want to sort customers by the last name in descending order, you use the DESC after the contactLastname column in the ORDER BY clause as shown in the following query:

SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname DESC;

B) Using MySQL ORDER BY clause to sort values in multiple columns example

If you want to sort the customers by the last name in descending order and then by the first name in ascending order, you specify both  DESC and ASC in the corresponding column as follows:

SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname DESC,
	contactFirstname ASC;

In this example, the ORDER BY  clause sorts the result set by the last name in descending order first and then sorts the sorted result set by the first name in ascending order to produce the final result set.

C) Using MySQL ORDER BY to sort a result set by an expression example

See the following  orderdetails table from the sample database.

The following query selects the order line items from the orderdetails table. It calculates the subtotal for each line item and sorts the result set based on the subtotal.

SELECT 
    orderNumber, 
    orderlinenumber, 
    quantityOrdered * priceEach
FROM
    orderdetails
ORDER BY 
   quantityOrdered * priceEach DESC;

To make the query more readable, you can assign the expression in the SELECT clause a column alias and use that column alias in the ORDER BY clause as shown in the following query:

SELECT 
    orderNumber,
    orderLineNumber,
    quantityOrdered * priceEach AS subtotal
FROM
    orderdetails
ORDER BY subtotal DESC;

In this example, we used subtotal as the column alias for the expression  quantityOrdered * priceEach and sorted the result set by the subtotal alias.

The column alias can be used in the ORDER BY clause because the SELECT a clause is evaluated before the ORDER BY clause. By the time the ORDER BY a clause is evaluated, the column alias is accessible.

Using MySQL ORDER BY to sort data using a custom list

The ORDER BY  the clause allows you to sort data using a custom list by using the FIELD()  function.

See the following orders table from the sample database.

Suppose that you want to sort the sales orders based on their statuses in the following order:

• In Process
• On Hold
• Canceled
• Resolved
• Disputed
• Shipped

To do this, you can use the FIELD() function to map each order status to a number and sort the result by the result of the FIELD() function:

SELECT 
    orderNumber, 
    status
FROM
    orders
ORDER BY 
    FIELD(status,
        'In Process',
        'On Hold',
        'Cancelled',
        'Resolved',
        'Disputed',
        'Shipped');

The following expression:

FIELD(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped');

returns the index of the status in the list 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped'.

For example, if the status is In Process, the function will return 1. If the status is On Hold, the function will return 2, and so on.

In this tutorial, you have learned how to use the MySQL ORDER BY clause to sort rows in the result set by one or more columns in ascending or descending order.