Order By In Mysql Desc Asc Query With Example

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 SQL DESC and ASC keywords are used together in conjunction with the SELECT statement and MySQL ORDER BY clause. The SQL DESC sort keyword has the following basic syntax. 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 – Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.

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 SQL sort by script that lists all the member records. Executing the above script gives the following results shown below.

“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.

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.