In MySQL, the ORDER BY Clause is used to sort the result set either in ascending order or descending order. By default, the ORDER BY sorts the displayed data in ascending order. If you want your data displayed in descending order we need to use the DESC keyword along with the ORDER BY Clause.

To make any analysis, sorted data can save much time and that can be achieved by ORDER BY Clause. It is very useful in organizing displayed data in a very meaningful way.

ORDER BY Clause

The ORDER BY Clause in MySQL is a powerful tool that allows you to sort the result set of a query in ascending or descending order based on one or more columns.

It is an essential part of querying databases when you want to retrieve data in a specific order. In this article, we will explore the syntax and usage of the MySQL ORDER BY Keyword.

Syntax:

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

Where,

• ASC (default): Sorts the result set in ascending order.
• DESC: Sorts the result set in descending order.

Demo MySQL Database

We will be using the following MySQL table for our examples on ORDER BY.

table – horje

To use this table on your system, write the following MySQL queries:

Create Table:

CREATE TABLE horje(
  user_id varchar(100) PRIMARY KEY,
  name varchar(100),
  rank int,
  courses_enrolled int,
  questions_solved int
 );

Insert the value in horje Table:

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('vish3001','Vishu',01,10,150);

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('neeraj119','Neeraj',02,09,125);

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('ayush105','Aayush',03,08,110);

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('sumit85','Sumit',04,07,100);

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('harsh05','Harsh',05,06,95);

ORDER BY Clause Examples

Now let’s look at some examples of the ORDER BY clause, and understand its workings in different scenarios.

Example 1: ORDER BY CLAUSE Using ASC/DESC Attribute

We can use the ASC attribute to sort in ascending order and the DESC attribute to sort in descending order. These are both very useful attributes of the ORDER BY clause.

• Using ASC Attribute

CASE 1: Let’s sort the displayed data in the table in ascending order for the “courses enrolled” column, but for this time we are going to use the ASC keyword along with the ORDER BY clause.

Query:

SELECT * from 
horje ORDER BY courses_enrolled ASC;

Output:

Result – CASE 01

Explanation: The query retrieves all columns from the horje table and sorts the results in ascending order based on the courses_enrolled column. Rows with fewer courses enrolled appear first, followed by those with more courses.

• Using DESC Attribute

CASE 2: Let’s sort the displayed data with respect to the courses enrolled column but this time we are displaying the data in descending order.

Query

SELECT * FROM 
horje ORDER BY courses_enrolled DESC;

Output:

Result – CASE 02

Explanation: The query retrieves all columns from the horje table and sorts the results in descending order based on the courses_enrolled column. Rows with fewer courses enrolled appear first, followed by those with more courses.

Example 2: ORDER BY CLAUSE With Multiple Columns

In this example, we are going to implement ORDER BY clause in multiple columns in a single query.

Before implementing this we will add some more data in our table with duplicate ranks for a clear understanding of how this will work with multiple columns.

Query:

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('vaibhav455','Vaibhav',05,08,110);

INSERT INTO horje(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('karan565','Karan',05,07,100);

Now let’s implement our query of the ORDER BY clause in multiple columns

Query:

SELECT * FROM horje
ORDER BY rank, name desc;

Output:

Output – Order By in Multiple Columns

Here sorting of data displayed is done on a priority basis. Let’s see how this works

• First, Sorting by rank in ascending order.
• For rows with equal rank, sorting by name in descending order.

We can conclude this, the first column which is mentioned, after the ORDER BY clause gets higher priority than the next mentioned column, and so on..

Example 3: ORDER BY CLAUSE With NULL VALUES

To implement this example we need to add some NULL values in the rank column.

Lets update rank column values to NULL for user id = ‘ayush105’ or user id = ‘harsh05’. We will use the UPDATE statement to achieve this task.

Query:

UPDATE horje
SET rank = NULL
WHERE user_id = 'ayush105' or user_id = 'harsh05';

Now let’s display our table values in ascending order with respect to the rank column.

Query:

SELECT * 
from horje ORDER BY rank;

Output:

Output – ORDER BY NULL Values

Explanation: In MYSQL, NULL values are considered lower than any other non-NULL values. In the above example, we can observe that all the rows with NULL values in their rank column appeared first followed by non-null values in ascending order.

Conclusion

The MySQL ORDER BY clause is used to sort the result set by one or more columns in ascending (ASC) or descending (DESC) order. It helps organize data in a meaningful way, making it easier to analyze and understand. Proper use of the ORDER BY clause enhances data retrieval efficiency and readability.

FAQs on MySQL ORDER BY Clause

What is the MySQL ORDER BY clause?

The ORDER BY clause is used to sort the result set of a query by one or more columns in ascending (ASC) or descending (DESC) order.

How do you sort results in ascending order using ORDER BY?

By default, the ORDER BY clause sorts results in ascending order. You can explicitly specify ascending order using ORDER BY column_name ASC.

How do you sort results in descending order using ORDER BY?

To sort results in descending order, use the DESC keyword: ORDER BY column_name DESC.

Can you sort by multiple columns using ORDER BY?

Yes, you can sort by multiple columns by listing them separated by commas: ORDER BY column1 ASC, column2 DESC.