ORDER BY in MySQL: DESC & ASC Query with EXAMPLE

Details

Last Updated: 28 December 2020

Sorting Results

Using the SELECT command, results were returned in the same order the records were added into the database. This is the default sort order. In this section, we will be looking at how we can sort our query results. Sorting is simply re-arranging our query results in a specified way. Sorting can be performed on a single column or on more than one column. It can be done on number, strings as well as date data types.

What is ORDER BY in MySQL?

MySQL ORDER BY is used in conjunction with the SELECT query to sort data in an orderly manner. The MySQL ORDER BY clause is used to sort the query result sets in either ascending or descending order.

SELECT statement... [WHERE condition | GROUP BY `field_name(s)` HAVING condition] ORDER BY `field_name(s)` [ASC | DESC];

HERE

"SELECT statement..." is the regular select query
" | " represents alternatives
"[WHERE condition | GROUP BY `field_name(s)` HAVING condition" is the optional condition used to filter the query result sets.
"ORDER BY" performs the query result set sorting
"[ASC | DESC]" is the keyword used to sort result sets in either ascending or descending order. Note ASC is used as the default.

What are DESC and ASC Keywords?

ASC is the short form for ascending	MySQL DESC is the short form for descending
It is used to sort the query results in a top to bottom style.	It is used to sort the query results in a bottom to top style
When working on date data types, the earliest date is shown on top of the list.	. When working on date types, the latest date is shown on top of the list.
When working with numeric data types, the lowest values are shown on top of the list.	When working with numeric data types, the highest values are shown at top of the query result set.
When working with string data types, the query result set is sorted from those starting with the letter A going up to the letter Z.	When working with string data types, the query result set is sorted from those starting with the letter Z going down to the letter A.

Both the SQL DESC and ASC keywords are used together in conjunction with the SELECT statement and MySQL ORDER BY clause.

DESC and ASC syntax

The SQL DESC sort keyword has the following basic syntax.

SELECT {fieldName(s) | *} FROM tableName(s) [WHERE condition] ORDER BY fieldname(s) ASC /DESC [LIMIT N]

HERE

SELECT {fieldName(s) | *} FROM tableName(s) is the statement containing the fields and table(s) from which to get the result set from.
[WHERE condition] is optional but can be used to filter the data according to the given condition.
ORDER BY fieldname(s) is mandatory and is the field on which the sorting is to be performed. The MySQL DESC keyword specifies that the sorting is to be in descending order.
[LIMIT] is optional but can be used to limit the number of results returned from the query result set.

Examples:

Let's now look at a practical example -

SELECT * FROM members;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.



		membership_number full_names gender date_of_birth physical_address postal_address contct_number email
		1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 This email address is being protected from spambots. You need JavaScript enabled to view it.
		2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL This email address is being protected from spambots. You need JavaScript enabled to view it.
		3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 This email address is being protected from spambots. You need JavaScript enabled to view it.
		4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL
		5 Leonard Hofstadter Male NULL Woodcrest NULL 845738767 NULL
		6 Sheldon Cooper Male NULL Woodcrest NULL 976736763 NULL
		7 Rajesh Koothrappali Male NULL Woodcrest NULL 938867763 NULL
		8 Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
		9 Howard Wolowitz Male 24-08-1981 SouthPark P.O. Box 4563 987786553

membership_number	full_names	gender	date_of_birth	physical_address	postal_address	contct_number	email
1	Janet Jones	Female	21-07-1980	First Street Plot No 4	Private Bag	0759 253 542	This email address is being protected from spambots. You need JavaScript enabled to view it.
2	Janet Smith Jones	Female	23-06-1980	Melrose 123	NULL	NULL	This email address is being protected from spambots. You need JavaScript enabled to view it.
3	Robert Phil	Male	12-07-1989	3rd Street 34	NULL	12345	This email address is being protected from spambots. You need JavaScript enabled to view it.
4	Gloria Williams	Female	14-02-1984	2nd Street 23	NULL	NULL	NULL
5	Leonard Hofstadter	Male	NULL	Woodcrest	NULL	845738767	NULL
6	Sheldon Cooper	Male	NULL	Woodcrest	NULL	976736763	NULL
7	Rajesh Koothrappali	Male	NULL	Woodcrest	NULL	938867763	NULL
8	Leslie Winkle	Male	14-02-1984	Woodcrest	NULL	987636553	NULL
9	Howard Wolowitz	Male	24-08-1981	SouthPark	P.O. Box 4563	987786553

Let's suppose the marketing department wants the members details arranged in decreasing order of Date of Birth. This will help them send birthday greetings in a timely fashion. We can get the said list by executing a query like below -

SELECT * FROM members ORDER BY date_of_birth DESC;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.

The same query in ascending order

SELECT * FROM members ORDER BY date_of_birth ASC

Note: NULL values means no values (not zero or empty string) . Observe the way they have been sorted.

More examples

Let's consider the following script that lists all the member records.

SELECT * FROM `members`;

Executing the above script gives the following results shown below.



		membership_number full_names gender date_of_birth physical_address postal_address contct_number email
		1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 This email address is being protected from spambots. You need JavaScript enabled to view it.
		2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL This email address is being protected from spambots. You need JavaScript enabled to view it.
		3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 This email address is being protected from spambots. You need JavaScript enabled to view it.
		4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL
		5 Leonard Hofstadter Male NULL Woodcrest NULL 845738767 NULL
		6 Sheldon Cooper Male NULL Woodcrest NULL 976736763 NULL
		7 Rajesh Koothrappali Male NULL Woodcrest NULL 938867763 NULL
		8 Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
		9 Howard Wolowitz Male 24-08-1981 SouthPark P.O. Box 4563 987786553 NULL

membership_number	full_names	gender	date_of_birth	physical_address	postal_address	contct_number	email
1	Janet Jones	Female	21-07-1980	First Street Plot No 4	Private Bag	0759 253 542	This email address is being protected from spambots. You need JavaScript enabled to view it.
2	Janet Smith Jones	Female	23-06-1980	Melrose 123	NULL	NULL	This email address is being protected from spambots. You need JavaScript enabled to view it.
3	Robert Phil	Male	12-07-1989	3rd Street 34	NULL	12345	This email address is being protected from spambots. You need JavaScript enabled to view it.
4	Gloria Williams	Female	14-02-1984	2nd Street 23	NULL	NULL	NULL
5	Leonard Hofstadter	Male	NULL	Woodcrest	NULL	845738767	NULL
6	Sheldon Cooper	Male	NULL	Woodcrest	NULL	976736763	NULL
7	Rajesh Koothrappali	Male	NULL	Woodcrest	NULL	938867763	NULL
8	Leslie Winkle	Male	14-02-1984	Woodcrest	NULL	987636553	NULL
9	Howard Wolowitz	Male	24-08-1981	SouthPark	P.O. Box 4563	987786553	NULL

Suppose we want to get a list that sorts the query result set using the gender field, we would use the script shown below.

SELECT * FROM `members` ORDER BY `gender`;



		membership_number full_names gender date_of_birth physical_address postal_address contct_number email
		1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 This email address is being protected from spambots. You need JavaScript enabled to view it.
		2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL This email address is being protected from spambots. You need JavaScript enabled to view it.
		4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL
		3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 This email address is being protected from spambots. You need JavaScript enabled to view it.
		5 Leonard Hofstadter Male NULL Woodcrest NULL 845738767 NULL
		6 Sheldon Cooper Male NULL Woodcrest NULL 976736763 NULL
		7 Rajesh Koothrappali Male NULL Woodcrest NULL 938867763 NULL
		8 Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
		9 Howard Wolowitz Male 24-08-1981 SouthPark P.O. Box 4563 987786553 NULL

membership_number	full_names	gender	date_of_birth	physical_address	postal_address	contct_number	email
1	Janet Jones	Female	21-07-1980	First Street Plot No 4	Private Bag	0759 253 542	This email address is being protected from spambots. You need JavaScript enabled to view it.
2	Janet Smith Jones	Female	23-06-1980	Melrose 123	NULL	NULL	This email address is being protected from spambots. You need JavaScript enabled to view it.
4	Gloria Williams	Female	14-02-1984	2nd Street 23	NULL	NULL	NULL
3	Robert Phil	Male	12-07-1989	3rd Street 34	NULL	12345	This email address is being protected from spambots. You need JavaScript enabled to view it.
5	Leonard Hofstadter	Male	NULL	Woodcrest	NULL	845738767	NULL
6	Sheldon Cooper	Male	NULL	Woodcrest	NULL	976736763	NULL
7	Rajesh Koothrappali	Male	NULL	Woodcrest	NULL	938867763	NULL
8	Leslie Winkle	Male	14-02-1984	Woodcrest	NULL	987636553	NULL
9	Howard Wolowitz	Male	24-08-1981	SouthPark	P.O. Box 4563	987786553	NULL

"Female" members have been displayed first followed by "Male" members, this is because when ORDER BY DESC clause is used without specifying the ASC or MySQL DESC keyword, by default, MySQL has sorted the query result set in an ascending order.

Let's now look at an example that does the sorting using two columns; the first one is sorted in ascending order by default while the second column is sorted in descending order.

SELECT * FROM `members` ORDER BY `gender`,`date_of_birth` DESC;

Executing the above script in MySQL workbench against the myflixdb gives the following results.

The gender column was sorted in ascending order by default while the date of birth column was sorted in descending order explicitly

Why we may use DESC and ASC?

Suppose we want to print a payments history for a video library member to help answer queries from the front desk, wouldn't it be more logical to have the payments printed in a descending chronological order starting with the recent payment to the earlier payment?

DESC in SQL is a keyword which becomes handy in such situations. We can write a query that sorts the list in descending order using the payment date.

Suppose the marketing department wants to get a list of movies by category that members can use to decide which movies are available in the library when renting movies, wouldn't it be more logical to look sort the movie category names and title in ascending so that members can quickly lookup the information from the list?

The ASC keyword comes in handy in such situations; we can get the movies list sorted by category name and movie title in an ascending order.

Summary

Sorting query results is re-arranging the rows returned from a query result set either in ascending or descending order.
The keyword DESC in SQL, is used to sort the query result set in a descending order.
The ASC keyword is used to sort the query result set in an ascending order.
Both DESC and ASC work in conjunction with the ORDER BY keyword. They can also be used in combination with other keywords such as WHERE clause and LIMIT
The default for ORDER BY when nothing has been explicitly specified is ASC.

SQL

MySQL Functions: String, Numeric, User-Defined, Stored

What are functions? MySQL can do much more than just store and retrieve data . We can also perform...

PL-SQL

Oracle PL/SQL Object Types Tutorial with EXAMPLES

What is Object Type in PL/SQL? Object-Oriented Programming is especially suited for building...

SQL

MYSQL Regular Expressions (REGEXP) with Syntax & Examples

What are regular expressions? Regular Expressions help search data matching complex criteria. We...

PL-SQL

Oracle PL/SQL Package: Type, Specification, Body [Example]

What is Package in Oracle? PL/SQL package is a logical grouping of a related subprogram...

SQL

MySQL UPDATE Query with Example

What is the UPDATE Query? UPDATE MySQL command is used to modify rows in a table. The update...

SQLite

SQLite INSERT, UPDATE, DELETE Query with Example

The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for...