Updated May 31, 2023
Introduction to MySQL ORDER BY DESC
MySQL ORDER BY DESC is an ORDER BY Clause in MySQL, which is responsible to sort the database records when fetched as the result rows.
Basically, the ORDER BY clause takes attributes to specify the sorting order of the table records. Therefore, we need to include the ASC or DESC modifiers with the ORDER BY clause for this query command.
The attributes, in combination with the ORDER BY clause, determine the arrangement of rows in the result table, whether in ascending or descending order. However, by default, the ORDER BY clause in MySQL automatically sets the value to ASC (ascending) if no modifier is provided. So, we will implement the ORDER BY DESC query to result from rows in descending order.
Syntax
The subsequent syntax is a simple structure for applying for ORDER BY DESC clause with MySQL database query:
SELECT ColumnName1, ColumnName2, ….FROM TableName WHERE Cond_Expr ORDER BY ColumnName1 DESC, ColumnName2DESC,….ColumnNameNDESC;
Let’s explain the syntax below:
- To apply for the ORDER BY DESC clause, we will use the SELECT statement for writing a query.
- ColumnName denotes the name of the table columns that the user wishes to fetch.
- TableName denotes the name of the table included in the query.
- The WHERE clause defines the conditional expression that the table applies to provide the result. This optional condition is used to determine which rows are displayed in the result set when it is satisfied.
- ORDER BY clause with DESC defines the ordering structure with a specific column name.
How does ORDER BY DESC work in MySQL?
- Implementing the SELECT statement in the MySQL query will fetch the data records from the specific table; then, you will notice that the result rows table provided is not in an ordered form. Therefore, to present the result set in an arrangement with a sorted list, we need to add MySQL ORDER BY clause with the SELECT query.
- With the help of the ORDER BY clause, you can order the rows with any specific column name. To further make the result displayed in a sorted form, i.e., increasing or decreasing order, we will use the keywords ASC or DESC, which defines the attributes for this ORDER BY clause.
- The ORDER BY Clause uses the DESC keyword to retrieve records in decreasing order. We can specify one or more table columns for sorting after the ORDER BY clause, separating the column list with commas. It’s important to note that different columns can be sorted differently using either the ASC or DESC keywords after ORDER BY, following the mentioned syntax.Like this one below:
SELECT ColumnName1, ColumnName2, …. FROM TableName ORDER BY ColumnName1 ASC, ColumnName2 DESC,….ColumnNameN [ASC | DESC];
Hence, this will sort the result rows by re-arranging them in a definite way.
Examples of MySQL ORDER BY DESC
Let us demonstrate some examples with the ORDER BY DESC clause and see its uses and working of it in MySQL:
Example #1 – MySQL ORDER BY DESC using one table column
Suppose, for illustration; we will take a sample table in our database named as Customer having the fields CusotmerID, CutomerName, Credit_Limit, and City. View the contents of the table as follows:
SELECT * FROM Customer;
Output:
Let us query data records from the Customer table to display the result using the ORDER BY clause simply without the attribute:
SELECT CustomerID, CustomerName, City FROM Customer ORDER BY CustomerName;
Output:
From the above output, it is clear that the result rows are sorted in ascending order by default, even when the ASC and DESC keywords are not explicitly specified.
Now, let us write a query using the MySQL ORDER BY DESC clause to sort the query result set by a particular table column in descending order:
SELECT CustomerID, CustomerName, City FROM Customer ORDER BY CustomerName DESC;
Output:
To observe the difference, you can utilize the DESC modifier with the ORDER BY clause, which sorts the contents in a descending order arrangement. For example, the CustomerName column in the query is specified with ORDER BY DESC.
Example #2 – MySQL ORDER BY DESC using multiple table columns
We will use the MySQL ORDER BY DESC clause with multiple columns of the table in the query and see the result set when executed. The MySQL statement querying on the same table Customer as the previous one is as follows:
SELECT CustomerID, CustomerName, City FROM Customer ORDER BY CustomerName DESC, City DESC;
Output:
Here, we have used DESC keywords with both the Column names after the ORDER BY clause to produce the result set in descending order. Thus, if one column is specified with the DESC modifier and the other column is not, the result will be automatically sorted in ascending order.
Example #3 – MySQL ORDER BY DESC using WHERE clause
Suppose we need to generate the result set with customers having a credit limit greater than or equal to 3000from the same query as using the ORDER BY DESC clause to fetch the result rows. We will query the records with the WHERE clause as follows:
SELECT CustomerName, City, Credit_LimitFROM Customer WHERE Credit_Limit>=3000 ORDER BY City DESC;
Output:
Example #4 – MySQL ORDER BY DESC using the LIMIT option
We can choose to apply the LIMIT clause, which allows us to restrict the number of rows fetched in the result set. We will use along with the ORDER BY DESC clause the LIMIT option at the end to see the results. Let us take the previous example and modify it a bit as below:
SELECT * FROM Customer WHERE Credit_Limit>=1500 ORDER BY Credit_Limit DESC;
Output:
Here, we have simply retrieved the results having 6 rows with an order by column credit limit and WHERE clause. Now, using LIMIT, the query becomes as follows:
SELECT * FROM Customer WHERE Credit_Limit>=1500 ORDER BY Credit_LimitDESC LIMIT 3;
Output:
Now, see the output; the result rows are reduced to 3 as mentioned, and the credit limit is in descending order, as mentioned in the query above.
Conclusion
The ORDER BY clause accompanied by the DESC modifier attribute defines the sorting order of the table rows when fetched with the query. If any attribute is not specified in the query for the ORDER BY clause then, the result rows will be in increasing order by default.
In MySQL, you can apply the ORDER BY DESC clause in SELECT LIMIT statements, SELECT statements, and DELETE LIMIT statements.
Recommended Articles
We hope that this EDUCBA information on “MySQL ORDER BY DESC” was beneficial to you. You can view EDUCBA’s recommended articles for more information.