Updated May 10, 2023
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, manipulating the table, fetching records from the table, etc.
These languages are segregated into the 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 to filter out or get structural data. ORDER BY is one of the clauses used to sort data in ascending or descending order.
Syntax:
We are used to sorting numeric and string values in ascending or descending manner. But by default, it sorts in an ascending way. If we want to descend, we need to specify it after using the ORDER BY clause.
Code:
SELECT column1,column2,column3 FROM table_name
ORDER BY column1;
(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 does ORDER BY Work 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 | City | Amount | |
1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
1005 | Hardik | Pandya | 5237658726 | [email protected] | Cuttack | 80000 |
1006 | Krunal | Pandya | 8765385386 | [email protected] | Bangalore | 50000 |
1007 | Jasprit | Bumrah | 3875636763 | [email protected] | Chennai | 78000 |
1008 | Anil | Kumble | 8735653786 | [email protected] | Kolkata | 12000 |
1009 | Ajinkya | Rahane | 8637578988 | [email protected] | Bhubaneswar | 87000 |
1010 | Ravi | Sashtri | 9796767676 | [email protected] | Kolkata | 56000 |
1011 | Rahul | Dravid | 3836768787 | [email protected] | Mumbai | 10000 |
1012 | Bhubaneswar | Kumar | 9736738787 | [email protected] | Chandigarh | 25000 |
As mentioned above, this order by clause can be applied to numeric and string values. In this example, id, contact, and amount contain numeric values. First_name, last_name, email, and city have 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 values on top of those attributes won’t make 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 the amount field in descending order
Output:
Cust_id | First_name | Last_name | Contact | City | Amount | |
1009 | Ajinkya | Rahane | 8637578988 | [email protected] | Bhubaneswar | 87000 |
1005 | Hardik | Pandya | 5237658726 | [email protected] | Cuttack | 80000 |
1007 | Jasprit | Bumrah | 3875636763 | [email protected] | Chennai | 78000 |
1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
1010 | Ravi | Sashtri | 9796767676 | [email protected] | Kolkata | 56000 |
1006 | Krunal | Pandya | 8765385386 | [email protected] | Bangalore | 50000 |
1012 | Bhubaneswar | Kumar | 9736738787 | [email protected] | Chandigarh | 25000 |
1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
1008 | Anil | Kumble | 8735653786 | [email protected] | Kolkata | 12000 |
1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
1011 | Rahul | Dravid | 3836768787 | [email protected] | 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 the amount field in ascending order.
Output:
Cust_id | First_name | Last_name | Contact | City | Amount | |
1011 | Rahul | Dravid | 3836768787 | [email protected] | Mumbai | 10000 |
1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
1008 | Anil | Kumble | 8735653786 | [email protected] | Kolkata | 12000 |
1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
1012 | Bhubaneswar | Kumar | 9736738787 | [email protected] | Chandigarh | 25000 |
1006 | Krunal | Pandya | 8765385386 | [email protected] | Bangalore | 50000 |
1010 | Ravi | Sashtri | 9796767676 | [email protected] | Kolkata | 56000 |
1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
1007 | Jasprit | Bumrah | 3875636763 | [email protected] | Chennai | 78000 |
1005 | Hardik | Pandya | 5237658726 | [email protected] | Cuttack | 80000 |
1009 | Ajinkya | Rahane | 8637578988 | [email protected] | 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 the first_name field in ascending order.
Output:
Cust_id | First_name | Last_name | Contact | City | Amount | |
1009 | Ajinkya | Rahane | 8637578988 | [email protected] | Bhubaneswar | 87000 |
1008 | Anil | Kumble | 8735653786 | [email protected] | Kolkata | 12000 |
1012 | Bhubaneswar | Kumar | 9736738787 | [email protected] | Chandigarh | 25000 |
1005 | Hardik | Pandya | 5237658726 | [email protected] | Cuttack | 80000 |
1007 | Jasprit | Bumrah | 3875636763 | [email protected] | Chennai | 78000 |
1006 | Krunal | Pandya | 8765385386 | [email protected] | Bangalore | 50000 |
1011 | Rahul | Dravid | 3836768787 | [email protected] | Mumbai | 10000 |
1010 | Ravi | Sashtri | 9796767676 | [email protected] | Kolkata | 56000 |
1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
(In the above query, Ajinkya and Anil start from A, but Ajinkya comes first, then Anil, because when the 1st letter is the same, the ORDER BY clause will sort it considering 2nd, 3rd,…. If other letters are the 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 the first_name field in descending order.
Output:
Cust_id | First_name | Last_name | Contact | City | Amount | |
1004 | Virendra | Shewag | 9087788988 | [email protected] | Delhi | 20000 |
1002 | Virat | Kohli | 8752877855 | [email protected] | Delhi | 60000 |
1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
1010 | Ravi | Sashtri | 9796767676 | [email protected] | Kolkata | 56000 |
1011 | Rahul | Dravid | 3836768787 | [email protected] | Mumbai | 10000 |
1006 | Krunal | Pandya | 8765385386 | [email protected] | Bangalore | 50000 |
1007 | Jasprit | Bumrah | 3875636763 | [email protected] | Chennai | 78000 |
1005 | Hardik | Pandya | 5237658726 | [email protected] | Cuttack | 80000 |
1012 | Bhubaneswar | Kumar | 9736738787 | [email protected] | Chandigarh | 25000 |
1008 | Anil | Kumble | 8735653786 | [email protected] | Kolkata | 12000 |
1009 | Ajinkya | Rahane | 8637578988 | [email protected] | Bhubaneswar | 87000 |
(Here, also the same thing happened. As in Anil and Ajinkya, N comes before J, so 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 | City | Amount | |
1011 | Rahul | Dravid | 3836768787 | [email protected] | Mumbai | 10000 |
1001 | Rohit | Sharma | 9876736587 | [email protected] | Mumbai | 10000 |
1008 | Anil | Kumble | 8735653786 | [email protected] | Kolkata | 12000 |
1003 | Sachin | Tendulkar | 9867868678 | [email protected] | Mumbai | 15000 |
1004 | Virendra | Shewag | 9087788988 | [email protected] | 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 LIMIT, WHERE, GROUP BY, etc., and window functions. While working on real-time examples like top customers in the database or the highest scorer in school, this ORDER BY clause helps a lot in sorting records per our requirements.
Recommended Articles
We hope that this EDUCBA information on “ORDER BY in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.