ORDER, ASC, DESC

Donald Green Donald Green
Feb 4, 2022 - 18:52
0 15
ORDER, ASC, DESC

Sort results

With the SELECT command, the results were returned in the same order in which the records were added to the database. This is the default sort order. In this section, we'll look at how we can sort the results of our queries. Sorting is simply reordering the results of our query in a specified way . Sorting can be done on a single column or on multiple columns. This can be done for numbers, strings, and date data types.

What is ORDER BY in MySQL?

MySQL ORDER BY is used in conjunction with a SELECT query to sort data in an ordered manner. The order by clause is used to sort query result sets in 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 a normal select query
  • "|"  presents alternatives
  • “[WHERE condition | GROUP BY" field_name(s) "HAVING condition" is an optional condition used to filter query result sets.
  • "ORDER BY" sorts the result set of the query
  • “[ASC | DESC]" is a keyword used to sort result sets in ascending or descending order. Note ASC is the default.

What are the keywords DESC and ASC?

ORDER IN MySQL: DESC & ASCASC is a short form for climbing

ORDER IN MySQL: DESC & ASCDESC is short form for descent

Used to sort query results in a top-to-bottom style.

Used to sort query results in bottom-up style

When working with date data types, the earliest date appears at the top of the list.

, When working with date types, the most recent date appears at the top of the list.

When working with numeric data types, the lowest values ​​appear at the top of the list.

When working with numeric data types, the highest values ​​appear at the top of the query result set.

When working with string data types, the query result set is sorted from those that start with the letter A and end with the letter Z.

When working with string data types, the query result set is sorted by those that begin with the letter Z and go down to the letter A.

 

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 a statement containing the fields and tables from which the result set can be obtained.
  • [WHERE condition] is optional, but can be used to filter data according to a specified condition.
  • ORDER BY field(s) is required and is the field on which the sort should be performed. The DESC keyword specifies that the sort should be in descending order.
  • [LIMIT] is optional, but can be used to limit the number of results returned from a query result set.

Examples:

Let us now look at a practical example −

SELECT * FROM members;
 

Running the above script in MySQL Workbench against myflixdb gives us the following results shown below.

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
one 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 <a href='mailto:jj@fstreet.com'>jj@fstre et.com</a>
3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 <a href='mailto:rm@tstreet.com'>rm@tstre et.com</a>
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
eight Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
9 Howard Wolowitz Male 24-08-1981 south park PO 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.

ORDER IN MySQL: DESC & ASC

The same query in ascending order

SELECT * FROM members ORDER BY date_of_birth ASC

ORDER IN MySQL: DESC & 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
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 <a href='mailto:janetjones@yagoo ;.cm'>janetjones@yagoo.cm</a>
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL <a href='mailto:jj@fstreet.com'>jj@fstre et.com</a>
3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 <a href='mailto:rm@tstreet.com'>rm@tstre et.com</a>
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
eight Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
9 Howard Wolowitz Male 24-08-1981 south park PO Box 4563 987786553 NULL

Suppose we want to get a list that sorts the result set of a query using the gender field, we will 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
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 <a href='mailto:janetjones@yagoo ;.cm'>janetjones@yagoo.cm</a>
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL <a href='mailto:jj@fstreet.com'>jj@fstre et.com</a>
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 <a href='mailto:rm@tstreet.com'>rm@tstre et.com</a>
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
eight Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
9 Howard Wolowitz Male 24-08-1981 south park PO Box 4563 987786553 NULL

The "female" items were displayed first, and then the "male" ones, because when using the order by clause without specifying the ASC or DESC keyword, MySQL sorts the query result set in ascending order by default.

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

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

Running the above script in MySQL Workbench against myflixdb gives the following results.

ORDER IN MySQL: DESC & ASC

By default the gender column was sorted in ascending order and the date of birth column in descending order

Why can we use DESC and ASC?

Suppose we want to print the payment history for a video library member to help answer requests from the front desk, wouldn't it make more sense to print the payments in descending order in chronological order, from most recent payment to older payment?

The DESC keyword comes in handy in such situations. We can write a query that sorts the list in descending order using the due date.

Let's say 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 at sorting titles and movie category names in ascending order so members can quickly search for information? from the list?

The ASC keyword comes in handy in these situations; we can sort the movie list by category name and movie name in ascending order.

Summary

  • Sorting query results is the reorganization of rows returned from a query result set in ascending or descending order.
  • The DESC keyword is used to sort the result set of a query in descending order.
  • The ASC keyword is used to sort the result set of a query in 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 the WHERE clause and LIMIT.
  • The default for ORDER BY when nothing is explicitly specified is ASC.