MySQL ORDER BY Clause

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.

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.

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

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

Create Table:

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

Insert the value in w3wiki Table:

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

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

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

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

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

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 
w3wiki ORDER BY courses_enrolled ASC;

Output:

Explanation: The query retrieves all columns from the w3wiki 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 
w3wiki ORDER BY courses_enrolled DESC;

Output:

Explanation: The query retrieves all columns from the w3wiki 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 w3wiki(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('vaibhav455','Vaibhav',05,08,110);

INSERT INTO w3wiki(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 w3wiki
ORDER BY rank, name desc;

Output:

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 w3wiki
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 w3wiki ORDER BY rank;

Output:

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.

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.

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.