ORDER BY Statement in MySQL | Sorting in MySQL

ORDER BY in MySQL

The ORDER BY clause is used in MySQL to sort the retrieved data in a particular order. When data is selected by the SELECT statement the data is not sorted in any order. The ORDER BY clause helps in sorting the fetched rows in ascending or descending order.

Syntax: 

SELECT column1, column2,...
FROM table_name 
ORDER BY Column1 ASC/DESC, Column2 ASC/DESC,... ;

• Here, In the above-shown syntax, it can be seen that there can be multiple columns after the ORDER BY clause and any of the columns can be sorted either in ascending or in descending order of their values.
• By default ORDER BY sorts the values of a column in ascending order. It means if there is nothing stated about the order of sorting (ASC or DESC) after the ORDER BY clause, it will be sorted in ascending order.
• For example;

1. SELECT column_name FROM table_name ORDER BY column_name ASC;
2. SELECT column_name FROM table_name ORDER BY column_name;

        The output of both queries 1 and 2 will be the same.

1. Using ORDER BY for only one column

The query written below uses the ORDER BY clause to sort the ‘city’ column in ascending order.

For example - SELECT city, state FROM cities ORDER BY city ASC;

Output:

It can be seen in the above table returned as an output, the ‘city’ column is sorted in ascending order. 

2. Using ORDER BY for multiple columns

The query written below will first sort the state column in ascending order and then the city column in descending order without changing the sorted order of the state column. 

For example - SELECT state, city FROM cities ORDER BY state ASC, city DESC;

Output:

In the above output, it can be seen that the output table is sorted in ascending order according to the ‘state’ column and there are multiple rows with the same value of ‘state’. Further, the output table is sorted in descending order of ‘city’ in the rows which have the same ‘state’.

3. Using ORDER BY with an expression

The ORDER BY clause can be used to sort the rows of a resulting table according to the value of an expression.

For example:

SELECT price, quantity, price*quantity FROM shop ORDER BY price*quantity;

Output:

4. Using ORDER BY with NULL value present in the column

While sorting a table in ascending order according to any column, if NULL appears in that column. It is placed before any non-NULL value. While sorting in descending order, NULL is placed after all non-NULL values.

For example:

SELECT product_id, price FROM shop ORDER BY price;

Output:

It can be seen in the above example that NULL appears before any non-NULL values when the ORDER BY clause is used to sort the rows of the resulting table in ascending order of price.