This tutorial explains MySQL ORDER BY clause and its usage with both the ASC and DESC options. We’ll describe this MySQL statement with the help of simple examples.
1. ORDER BY Clause Syntax
2. ORDER BY W/O ASC or DESC
3. ORDER BY DESC
4. ORDER BY Relative Position
5. ORDER BY Two Fields ASC & DESC
Let’s now go through each of the sections one by one.
MySQL ORDER BY Clause
The MySQL SELECT command doesn’t return a sorted result set by default. Hence, to sort the output, you have to append the ORDER BY clause in the SELECT query. We can achieve the following by using the ORDER BY clause:
- Perform sorting of records by a single/multiple fields
- Sort the rows by distinct columns in ascending (ASC) or descending (DESC) order.
The following illustrates the syntax of the ORDER BY clause:
Syntax
# MySQL ORDER BY Clause SELECT field1, field2,... FROM target_table ORDER BY field1 [ASC|DESC], field2 [ASC|DESC],...
The ORDER BY clause by default performs sorting in ascending order. The term ASC is an acronym for increasing while DESC means descending. For DESC, you have to mention it in your SELECT query.
Please note that you can even specify an expression with the ORDER BY in MySQL. Also, both the options ASC and DESC are optional to this statement.
Here are some sample exercises for practice.
MySQL ORDER BY Clause Examples
Let’s check out how to use the ORDER BY clause with MySQL SELECT queries.
Using order by without asc/desc option
To demonstrate, let’s first create a table named Sample and populate it with some test data.
CREATE TABLE Sample
(
sample_id int NOT NULL,
sample_name VARCHAR(20),
vendor_name VARCHAR(20),
sample_size INT
);
Next, let’s insert some dummy data for our testing.
INSERT INTO Sample
(
sample_id, sample_name, vendor_name, sample_size
)
VALUES
(1, 'First Sample', 'Walmart', 100),
(2, 'Second Sample', 'Twitter', 1000),
(3, 'Third Sample', 'Google', 300),
(4, 'Fourth Sample', 'Apple', 4000);
After this, we need to check the content of our Sample table. See below.
SELECT
*
FROM
Sample;
Its output is as follows:
ID Sample Name Vendor Size ------------------------------------- 1 First Sample Walmart 100 2 Second Sample Twitter 1000 3 Third Sample Google 300 4 Fourth Sample Apple 4000
Now, we’ll exercise the ORDER BY clause on the “vendor_name” filed. And, we’ll observe the result set received from the MySQL database:
SELECT
*
FROM
Sample
ORDER BY vendor_name;
You’ll get a result showing the below output:
ID Sample Name ↑Vendor Size ------------------------------------- 4 Fourth Sample Apple 4000 3 Third Sample Google 300 2 Second Sample Twitter 1000 1 First Sample Walmart 100
You can assess from the above result set that sorting took place in the default ascending order.
Related Topic – WHERE Clause in SQL: A Practical Guide
Sort in descending order
In this example, we’ll demonstrate the ORDER BY clause to sort in the descending order. For this, we’ll have to specify it explicitly.
Here is the MySQL statement to sort in the descending order:
SELECT
*
FROM
Sample
ORDER BY vendor_name DESC;
The output of SELECT is:
ID Sample Name ↓Vendor Size ------------------------------------- 1 First Sample Walmart 100 2 Second Sample Twitter 1000 3 Third Sample Google 300 4 Fourth Sample Apple 4000
Sort by field on a relative position
It is possible to use MySQL ORDER BY clause to sort by a field on a RELATIVE position.
When we specify column names in the SELECT query, the first field is at position one, second on two, and so on. Moreover, we can validate this fact using an example.
Here is a SELECT statement that displays fields and sorts by a relative position.
SELECT
# pos_1 pos_2 pos_3 pos_4
sample_id, sample_name, vendor_name, sample_size
FROM
Sample
ORDER BY 4 DESC;
Here is the result set that you get after running the above command:
ID Sample Name Vendor ↓Size ------------------------------------- 4 Fourth Sample Apple 4000 2 Second Sample Twitter 1000 3 Third Sample Google 300 1 First Sample Walmart 100
You can see that MySQL sorted by sample_size field which corresponds to the fourth column.
Using ORDER BY to sort on two columns
We had mentioned earlier that you could use ORDER BY on one or more fields. In such a case, MySQL treats the first field as primary and the latter as secondary. Therefore, it first sorts the primary and then the second one.
Hence, in this example, we’ll demonstrate ORDER BY on two columns, one field as ASC, and another DESC. Also, we need to insert more records to show that the second column gets sorted too.
You can ignore the INSERT statement given at the beginning above and use the one below.
INSERT INTO Sample
(
sample_id, sample_name, vendor_name, sample_size
)
VALUES
(1, 'First Sample', 'Walmart', 100),
(2, 'Second SampleI', 'Twitter', 1000),
(2, 'Second SampleII', 'Twitter', 9000),
(3, 'Third SampleI', 'Google', 400),
(3, 'Third SampleII', 'Google', 800),
(4, 'Fourth Sample', 'Apple', 4000);
Now, we have a few rows with the same vendor names but varied sample sizes. And, we’ll now sort based upon vendor_name (primary) and sample_size (secondary) fields.
SELECT
sample_id, sample_name, vendor_name, sample_size
FROM
Sample
ORDER BY vendor_name ASC, sample_size DESC;
This MySQL statement produces this result:
ID Sample Name ↑Vendor ↓Size ------------------------------------- 4 Fourth Sample Apple 4000 3 Third SampleII Google ↓800 3 Third SampleI Google ↓400 2 Second SampleII Twitter ↓9000 2 Second SampleI Twitter ↓1000 1 First Sample Walmart 100
You can see all the values in the primary field got sorted in the ascending order. And, it is also evident from the output that sorting (in DESC) took place on the second one as well.
Now, let’s find out what would be the result, had we sorted only on the vendor name.
SELECT
sample_id, sample_name, vendor_name, sample_size
FROM
Sample
ORDER BY vendor_name ASC;
Also Check – Quick Steps to Install MySQL on Windows
This command will yield as:
ID Sample Name ↑Vendor Size ------------------------------------- 4 Fourth Sample Apple 4000 3 Third SampleI Google (x)400 3 Third SampleII Google 800 2 Second SampleI Twitter (x)1000 2 Second SampleII Twitter 9000 1 First Sample Walmart 100
We can see from the above result that rows belonging to Google and Twitter didn’t sort on their sample sizes.
We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL ORDER BY clause. However, you may practice more with examples to gain confidence.
Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.
