(SQL examples for Beginners)
In this end-to-end example, you will learn – SQL Tutorials for Business Analyst: MySQL Tutorials for Business Analyst: ORDER BY in MySQL: DESC and ASC.
ORDER BY in MySQL: DESC & ASC
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 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?
Both the DESC and ASC keywords are used together in conjunction with the SELECT statement and the ORDER BY clause.
DESC and ASC syntax
The 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 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 | |
---|---|---|---|---|---|---|---|
1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | jj@fstreet.com |
3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | NULL | 12345 | rm@tstreet.com |
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 | |
---|---|---|---|---|---|---|---|
1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | jj@fstreet.com |
3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | NULL | 12345 | rm@tstreet.com |
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 | |
---|---|---|---|---|---|---|---|
1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | jj@fstreet.com |
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 | rm@tstreet.com |
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 clause is used without specifying the ASC or 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?
The DESC key word comes in 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 DESC keyword 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.
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.
MySQL Tutorials for Business Analyst: MySQL INSERT INTO Query: How to add Row in Table
MySQL Tutorials for Business Analyst: MySQL GROUP BY and HAVING Clause
Excel formula for Beginners – How to Sort by one column in Excel