MySQL ORDER BY Clause

In MySQL, 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 – geeksforgeeks

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

Create Table

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

Fill Values

INSERT INTO geeksforgeeks(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('vish3001','Vishu',01,10,150);
INSERT INTO geeksforgeeks(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('neeraj119','Neeraj',02,09,125);
INSERT INTO geeksforgeeks(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('ayush105','Aayush',03,08,110);
INSERT INTO geeksforgeeks(user_id,name,rank,courses_enrolled,questions_solved)
VALUES('sumit85','Sumit',04,07,100);
INSERT INTO geeksforgeeks(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.

A) 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 
geeksforgeeks ORDER BY courses_enrolled ASC;

Output:

Result – CASE 01

In the above image, we can clearly observe that displayed data is sorted in ascending order of the courses_enrolled column. We can see row with the lowest courses i.e. 6 is displayed first followed by 7, 8, and so on…

B) 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 
geeksforgeeks ORDER BY courses_enrolled DESC;

Output:

Result – CASE 02

We can observe that our data is displayed in descending order with respect to the courses enrolled column. We can clearly see the row with the highest courses i.e. 10 is displayed first followed by 9, 8, and so on…

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 clear understanding of how this will work with multiple columns.

Query

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

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

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

Query

SELECT * FROM geeksforgeeks
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 geeksforgeeks
SET rank = NULL
WHERE user_id = 'ayush105' or user_id = 'harsh05';

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

Query

SELECT * 
from geeksforgeeks ORDER BY rank;

Output:

Output – ORDER BY NULL Values

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

Key TakeAways:

• ORDER BY Keyword is used to sort the result set of a query by one or more columns.
• You can specify ascending (ASC) or descending (DESC) order, with ascending being the default.
• Sorting can be done on more than one column, and the priority is given to the first column specified, then the next, and so on.
• Performance can be affected, especially with large data sets, so indexing is crucial for optimizing speed.