MySQL Order By

MySQL Order By clause is used to sort the table data in either Ascending or Descending order. By default, data is not inserted into Tables in any sequence unless you have an index.

So, If you want to retrieve the data in any particular sequence, you have to sort it by using MySQL Order By statement. The basic syntax of the MySQL Order By clause is as shown below:

SELECT Column_Names
FROM Table_Name
ORDER BY {Column, Numerical Position, Alias Columns}[ASC | DESC]

From the above MySQL order by syntax, you can observe that we can use the Column Name, or Numerical Position of the Column Name, or an Alias Column Name in the Order By statement. We are going to use the below-shown data to explain the MySQL Order By statement with example.

MySQL Order By Asc 1

MySQL Order By Ascending

To sort data in ascending, we have to use MySQL Order By statement, followed by the ASC keyword. The following are the list of ways to sort data in ascending. For example, as a Sales Manager, If you want to identify the low performing products (Products with No Sales, or fewer sales), then you can use this:

By seeing the result, you can understand that x is not performing well, and y has no sales at all. Using this data, you can try different strategies to improve sales.

MySQL Sort in Ascending Order Example

In this MySQL example, we are going to sort the Data in Ascending using Yearly Income. Please replace yearly income with any of your required columns in a Table.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, Yearly_Income, Sales 
FROM customers
ORDER BY Yearly_Income ASC;

From the below screenshot, you can see the Data was sorted by Yearly Income in Ascending.

MySQL Order By Asc 3

Sort in Ascending Order without using ASC

The ASC keyword is the default keyword in MYSQL Order By statement, that’s why it is optional to use ASC. In this example, we are going to sort the customer’s data by First_Name in ascending without using ASC Keyword.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, Yearly_Income, Sales 
FROM customers
ORDER BY First_Name;
Sort Records without using ASC 2

Sort Numeric Position in Ascending

In this MySQL Order By ASC example, We are going to sort customers table in Ascending using Numerical Position of a Column Name.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, Yearly_Income, Sales 
FROM customers
ORDER BY 2 ASC;

The Numerical position of Last_Name is 2. So, data sorted by this column.

MySQL Order By Asc 4

Order By Multiple Columns in Ascending

In this MySQL Order By ASC example, we are sorting the Data using multiple columns.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, Yearly_Income, Sales 
FROM customers
ORDER BY First_Name ASC, Last_Name ASC;

--OR you can Simply Write 
ORDER BY First_Name, Last_Name

First, data sorted in Ascending by First Name. The Last Name then sorts it in Ascending.

Sort by multiple columns in ascending 5

MySQL Sort By ASC using Alias Column

In this MySQL Order by ASC example, we are going to sort the customer’s Data in Ascending using Alias Column Name.

USE company;
SELECT CONCAT(First_Name, ' ', Last_Name) AS 'Name', 
       Education, Profession, Yearly_Income, Sales 
FROM customers
ORDER BY 'Name' ASC;

We used the concat string function to concat the First Name, and Last_Name columns to create Name (an Alias Name). Next, we used the Alias name in it. It means customer’s data sort by Name in Ascending.

MySQL Order By Asc 6

MySQL ORDER BY Descending

To sort data in Descending, use MySQL Order By statement followed by the DESC keyword. The following are the list of ways we can display records in Descending.

For example, If you are searching for shoes on Amazon. If you type shoe in the search bar, it displays shoes by Rating. It means, Shoes are displayed as per the Rating. Technically,

Select Shoe 
from Product table
order by Product Rating desc

MySQL Sort in Descending Example

In this MySQL Order By Desc example, we are going to display customer’s table in Descending using the Sales column.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, 
       Sales 
FROM customers
ORDER BY Sales DESC;
Sort by Descending DESC 7

From the above screenshot, you can see that the rows are organized by Sales in Descending.

Sort in Descending using Numerical Position

The Numerical position of Education column is 3. So, data uses this column to display in Descending.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, 
       Sales 
FROM customers
ORDER BY 3 DESC;
MySQL Order By DESC 8

MySQL Order By Multiple Columns in Descending

In this example, we are organizing the rows using multiple columns. First, by Education in Descending and then by Yearly Income in Descending.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, 
       Sales 
FROM customers
ORDER BY 3 DESC, Yearly_Income DESC;
MySQL Order By DESC 9

In this example, We are going to organize the table rows in Descending using the Alias Column Name.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, Yearly_Income + 12500 AS 'New Income',
       Sales 
FROM customers
ORDER BY 'New Income' DESC;

We added 12500 to each yearly income column and used Alias to assign a New Income name. Next, we used the Alias name. It means, rows organized by New Income in Descending.

MySQL Order By DESC 10

Combine ASC and DESC

We can also combine both ASC and DESC keywords in a single statement. In this example, we are organizing Education in Ascending, and then by Yearly Income in Descending.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, 
       Sales 
FROM customers
ORDER BY Education ASC, Yearly_Income DESC;
MySQL Order By DESC 11

Let us use the DESC keyword first, and ASC keyword second. It means, below query organize the records by Education in Descending, and Yearly Income in Ascending

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, 
       Sales 
FROM customers
ORDER BY Education DESC, Yearly_Income ASC;
MySQL Order By DESC 12

Command Prompt Example

Let me show you how to sort records using the command prompt. In this example, we are sorting the First Name and Last Name in Ascending, then Yearly Income in Descending.

USE company;
SELECT First_Name, Last_Name, 
       Education, Profession, 
       Yearly_Income, 
       Sales 
FROM customers
ORDER BY First_Name ASC, Last_Name ASC, Yearly_Income DESC;
MySQL Order By DESC 13