ORDER BY in MySQL

Introduction to ORDER BY in MySQL

The following article provides an outline for ORDER BY in MySQL. In SQL, we use several kinds of language for different purposes like defining the table at the time of creation, manipulation in the table, fetching records from the table, etc.

These languages are segregated into following:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DQL (Data Query Language)
• DCL (Data Control Language)
• TCL (Transaction Control Language)

DQL is used for data retrieval purposes by using the SELECT command. With SELECT, we use many clauses for filtering out or for getting structural data. ORDER BY is one of the clauses used to sort out data in either ascending or descending order.

Syntax:

We are used to sort both numeric and string values either in ascending or descending manner. But by default, it sorts in ascending manner. If we want to descend, then we need to specify it after using the ORDER BY clause.

Code:

SELECT column1,column2,column3 FROM table_name
ORDER BY column1;>/code>

(This will sort column1 in ascending order)

Code:

SELECT column1,column2,column3 FROM table_name
ORDER BY column1 DESC;

(This will sort column1 in descending order)

How ORDER BY Works in MySQL?

Given below shows how ORDER BY works in MySQL:

Example:

Let’s create one customer table having the following attributes:

Cust_id	First_name	Last_name	Contact	Email	City	Amount
1001	Rohit	sharma	9876736587	rohit@gmail.com	Mumbai	10000
1002	Virat	Kohli	8752877855	virat@tahoo.in	Delhi	60000
1003	Sachin	Tendulkar	9867868678	sachin@hotmail.com	Mumbai	15000
1004	Virendra	Shewag	9087788988	shewag@gmail.com	Delhi	20000
1005	Hardik	Pandya	5237658726	ahskjs@hmail.com	Cuttack	80000
1006	Krunal	Pandya	8765385386	krunal@gmail.com	Bangalore	50000
1007	Jasprit	Bumrah	3875636763	jasprt@gmail.com	Chennai	78000
1008	Anil	Kumble	8735653786	anil@gmail.com	Kolkata	12000
1009	Ajinkya	Rahane	8637578988	ajin@gmail.com	Bhubaneswar	87000
1010	Ravi	Sashtri	9796767676	ravi@gmail.com	Kolkata	56000
1011	Rahul	Dravid	3836768787	rahul@gmail.com	Mumbai	10000
1012	Bhubaneswar	Kumar	9736738787	bb@gmail.com	Chandigarh	25000

As we have mentioned above, this order by clause can be applied both to numeric and string values. Here in this example id, contact and amount contain numeric values. First_name, last_name, email and city contain string values. We are going to apply ORDER BY clauses to the amount and first_name. Though contact and id contain numeric, and the email contains string values, sorting value on top of those attributes won’t make any sense.

In ascending and descending order “ORDER BY” will sort numerically from 1 to infinity and string values from A to Z and vice versa.

Example #1

Code:

SELECT * FROM customer
ORDER BY amount DESC;

It will show the output by sorting amount field in descending order

Output:

Cust_id	First_name	Last_name	Contact	Email	City	Amount
1009	Ajinkya	Rahane	8637578988	ajin@gmail.com	Bhubaneswar	87000
1005	Hardik	Pandya	5237658726	ahskjs@hmail.com	Cuttack	80000
1007	Jasprit	Bumrah	3875636763	jasprt@gmail.com	Chennai	78000
1002	Virat	Kohli	8752877855	virat@tahoo.in	Delhi	60000
1010	Ravi	Sashtri	9796767676	ravi@gmail.com	Kolkata	56000
1006	Krunal	Pandya	8765385386	krunal@gmail.com	Bangalore	50000
1012	Bhubaneswar	Kumar	9736738787	bb@gmail.com	Chandigarh	25000
1004	Virendra	Shewag	9087788988	shewag@gmail.com	Delhi	20000
1003	Sachin	Tendulkar	9867868678	sachin@hotmail.com	Mumbai	15000
1008	Anil	Kumble	8735653786	anil@gmail.com	Kolkata	12000
1001	Rohit	sharma	9876736587	rohit@gmail.com	Mumbai	10000
1011	Rahul	Dravid	3836768787	rahul@gmail.com	Mumbai	10000

Example #2

Code:

SELECT * FROM customer
ORDER BY amount;

(If we don’t specify anything ascending or descending, then by default it will sort by ascending). It will show the output by sorting amount field in ascending order.

Output:

Cust_id	First_name	Last_name	Contact	Email	City	Amount
1011	Rahul	Dravid	3836768787	rahul@gmail.com	Mumbai	10000
1001	Rohit	sharma	9876736587	rohit@gmail.com	Mumbai	10000
1008	Anil	Kumble	8735653786	anil@gmail.com	Kolkata	12000
1003	Sachin	Tendulkar	9867868678	sachin@hotmail.com	Mumbai	15000
1004	Virendra	Shewag	9087788988	shewag@gmail.com	Delhi	20000
1012	Bhubaneswar	Kumar	9736738787	bb@gmail.com	Chandigarh	25000
1006	Krunal	Pandya	8765385386	krunal@gmail.com	Bangalore	50000
1010	Ravi	Sashtri	9796767676	ravi@gmail.com	Kolkata	56000
1002	Virat	Kohli	8752877855	virat@tahoo.in	Delhi	60000
1007	Jasprit	Bumrah	3875636763	jasprt@gmail.com	Chennai	78000
1005	Hardik	Pandya	5237658726	ahskjs@hmail.com	Cuttack	80000
1009	Ajinkya	Rahane	8637578988	ajin@gmail.com	Bhubaneswar	87000

Example #3

Code:

SELECT * FROM customer
ORDER BY first_name;

(If we don’t specify anything ascending or descending, then by default it will sort by ascending).

It will show the output by sorting first_name field in ascending order.

Output:

Cust_id	First_name	Last_name	Contact	Email	City	Amount
1009	Ajinkya	Rahane	8637578988	ajin@gmail.com	Bhubaneswar	87000
1008	Anil	Kumble	8735653786	anil@gmail.com	Kolkata	12000
1012	Bhubaneswar	Kumar	9736738787	bb@gmail.com	Chandigarh	25000
1005	Hardik	Pandya	5237658726	ahskjs@hmail.com	Cuttack	80000
1007	Jasprit	Bumrah	3875636763	jasprt@gmail.com	Chennai	78000
1006	Krunal	Pandya	8765385386	krunal@gmail.com	Bangalore	50000
1011	Rahul	Dravid	3836768787	rahul@gmail.com	Mumbai	10000
1010	Ravi	Sashtri	9796767676	ravi@gmail.com	Kolkata	56000
1001	Rohit	sharma	9876736587	rohit@gmail.com	Mumbai	10000
1003	Sachin	Tendulkar	9867868678	sachin@hotmail.com	Mumbai	15000
1002	Virat	Kohli	8752877855	virat@tahoo.in	Delhi	60000
1004	Virendra	Shewag	9087788988	shewag@gmail.com	Delhi	20000

(In above query we can see Ajinkya and Anil starts from A, but Ajinkya comes first then Anil, because when 1^st letter are same, ORDER BY clause will sort it considering 2^nd, 3^rd,…. If other letters are same. Here J comes before N in Ajinkya and Anil respectively. That’s why it gives the output like this.)

Example #4

Code:

SELECT * FROM customer
ORDER BY first_name DESC;

It will show the output by sorting first_name field in descending order.

Output:

Cust_id	First_name	Last_name	Contact	Email	City	Amount
1004	Virendra	Shewag	9087788988	shewag@gmail.com	Delhi	20000
1002	Virat	Kohli	8752877855	virat@tahoo.in	Delhi	60000
1003	Sachin	Tendulkar	9867868678	sachin@hotmail.com	Mumbai	15000
1001	Rohit	sharma	9876736587	rohit@gmail.com	Mumbai	10000
1010	Ravi	Sashtri	9796767676	ravi@gmail.com	Kolkata	56000
1011	Rahul	Dravid	3836768787	rahul@gmail.com	Mumbai	10000
1006	Krunal	Pandya	8765385386	krunal@gmail.com	Bangalore	50000
1007	Jasprit	Bumrah	3875636763	jasprt@gmail.com	Chennai	78000
1005	Hardik	Pandya	5237658726	ahskjs@hmail.com	Cuttack	80000
1012	Bhubaneswar	Kumar	9736738787	bb@gmail.com	Chandigarh	25000
1008	Anil	Kumble	8735653786	anil@gmail.com	Kolkata	12000
1009	Ajinkya	Rahane	8637578988	ajin@gmail.com	Bhubaneswar	87000

(Here also same thing happened. As in Anil and Ajinkya, N comes before J, that’s why it came Anil came first then Ajinkya.)

There is another clause we use with ORDER BY, which is LIMIT.

Example #5

Code:

SELECT * FROM customer
ORDER BY first_name
LIMIT 5;

Output:

Cust_id	First_name	Last_name	Contact	Email	City	Amount
1011	Rahul	Dravid	3836768787	rahul@gmail.com	Mumbai	10000
1001	Rohit	sharma	9876736587	rohit@gmail.com	Mumbai	10000
1008	Anil	Kumble	8735653786	anil@gmail.com	Kolkata	12000
1003	Sachin	Tendulkar	9867868678	sachin@hotmail.com	Mumbai	15000
1004	Virendra	Shewag	9087788988	shewag@gmail.com	Delhi	20000

(It will only fetch the top 5 records as we have specified LIMIT = 5. )

Conclusion – ORDER BY in MySQL

Order by clause can be used among many other clauses like LIMIT, WHERE, GROUP BY, etc and this is also used in window functions. While working on real-time examples like top customers in the database or highest scorer in school, this ORDER BY clause helps a lot for sorting records as per our requirement.

Recommended Articles

This is a guide to the ORDER BY in MySQL. Here we discuss the introduction and how ORDER BY works in MySQL with query examples. You can also go through our other suggested articles to learn more –

1. ORDER BY Clause in SQL
2. Joins in MySQL
3. MySQL Relational Database
4. Introduction to MySQL