ORDER BY in MySQL

Introduction to ORDER BY in MySQL

The following article will provide you the 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 followings:

1. DDL (Data definition language)
2. DML (Data manipulation language)
3. DQL (Data query language)
4. DCL (Data control language)
5. 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 which is used for sorting out data in either ascending or descending order.

Syntax:

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.

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

(This will sort column1 in ascending order)

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

(This will sort column1 in descending order)

How ORDER BY Works in MySQL?

Example:

Let’s create one customer table having the following attributes

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

SELECT * FROM customer
ORDER BY amount DESC;

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

Output:

Example #2

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:

Example #3

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:

(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

SELECT * FROM customer
ORDER BY first_name DESC;

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

Output:

(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

SELECT * FROM customer
ORDER BY first_name
LIMIT 5;

Output:

(it will only fetch 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 –

1. ORDER BY Clause in SQL
2. Joins in MySQL
3. MySQL Relational Database
4. Introduction to MySQL
5. Guide to Relational Database
6. ORDER BY in Oracle | How to Work?
7. Tableau Window Functions | How to Work?
8. Data Control Language | How it Works? | Need
9. Complete Guide to Data Definition Language
10. Transaction Control Language | Examples