EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial ORDER BY in MySQL

ORDER BY in MySQL

By Priya PedamkarPriya Pedamkar

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, manipulating the table, fetching records from the table, etc.

These languages are segregated into the following:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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 Email 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 Email 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 Email 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 Email 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 Email 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 Email 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.

  1. ORDER BY Clause in SQL
  2. Joins in MySQL
  3. MySQL Relational Database
  4. Introduction to MySQL
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more