MySQL Order By

MySQL Order By clause is used to sort the table data in either Ascending order or Descending order. By default data will not be inserted into Tables in any order unless you have an index. So, If you want to retrieve the data in any particular order, you have to sort it by using MySQL Order By statement.

MySQL Order By Syntax

The basic syntax of Order By in MySQL is as shown below:

1 2 3

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

From the above 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 Ascending

In order to sort data in Ascending order, we have to use Order By statement followed by ASC keyword. In MySQL, Following are the list of ways we can sort data in Ascending order.

For example, as a Sales Manager, If you want to identify the low performing products (Products with No Sales, or less sales) then you can write the query as:

1 2	Select Product Name From Products Table Order By Sales Asc.

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 example, we are going to sort the Data in Ascending Order using Yearly Income. Please replace yearly income with any of your required column.

1 2 3 4 5 6

-- MySQL Order By Asc Example 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, Data is sorted by Yearly Income in Ascending order.

MySQL Sort in Ascending Order – without using ASC

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

1 2 3 4 5 6

-- MySQL Order By Asc Example USE company; SELECT First_Name, Last_Name,        Education, Profession, Yearly_Income, Sales FROM customers ORDER BY First_Name;

OUTPUT

MySQL Sort in Ascending Order – Use Numerical Position

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

1 2 3 4 5 6

-- MySQL Order By Asc Example 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 will be sorted by this column.

MySQL Order By Multiple Columns in Ascending Order

In this example we are sorting the Data using multiple columns.

1 2 3 4 5 6 7 8 9

-- MySQL Order By ASC Example 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 will be sorted by First Name in Ascending Order, and then data will be sorted by Last Name in Ascending Order

MySQL Sort By ASC – Using Alias Column

In this example, we are going to sort the customers Data in Ascending Order using Alias Column Name.

1 2 3 4 5 6

-- MySQL Order By Asc Example 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 First Name, and Last_Name columns to create Name (an Alias Name). Next, we used the Alias name in the ORDER BY Clause. It means, customers data will be sorted by Name in Ascending Order.

SQL ORDER BY Descending

In order to sort data in Descending order, we have to use Order By statement followed by DESC keyword. In MySQL, Following are the list of ways we can sort data in Descending order.

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

1 2 3

Select Shoe from Product table order by Product Rating desc

MySQL Sort in Descending Order Example

In this example, We are going to sort customers data in Descending Order using Sales column.

1 2 3 4 5 6 7 8

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

From the above screenshot you can see, data is sorted by Sales in Descending order.

MySQL Sort in Descending Order – Use Numerical Position

The Numerical position of Education column is 3. So, data will be sorted by this column in Descending order

1 2 3 4 5 6 7 8

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

OUTPUT

MySQL Order By Multiple Columns in Descending Order

In this example we are sorting the Data using multiple columns. First, data will be sorted by Education in Descending Order, and then data will be sorted by Yearly Income in Descending Order

1 2 3 4 5 6 7 8

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

OUTPUT

MySQL Sort in Descending Order – Using Alias Column

In this example, We are going to sort the table Data in Descending Order using the Alias Column Name.

1 2 3 4 5 6 7 8

-- MySQL Order By DESC Example 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 and every yearly income column, and used Alias to assign New Income name. Next, we used the Alias name in the Order By clause. It means, Data will be sorted by New Income in Descending Order.

Combine MySQL Order By ASC and DESC

MySQL Sort allows us to combine both ASC, and DESC keywords in a single statement. In this example we are sorting Education in Ascending Order, and then sorting Yearly Income in Descending Order

1 2 3 4 5 6 7 8

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

OUTPUT

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

1 2 3 4 5 6 7 8

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

OUTPUT

MySQL Order By Example – Command Prompt

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 Order, and then we are sorting Yearly Income in Descending Order

1 2 3 4 5 6 7 8

-- MySQL Order By DESC and ASC Example 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;

OUTPUT