In this example, we use subtotal as the column alias for the expression quantityOrdered * priceEach and sort the result set by the subtotal alias. | orderNumber | orderLineNumber | subtotal | ORDER BY subtotal DESC Code language: SQL (Structured Query Language) ( sql ) To make the query more readable, you can assign the expression in the SELECT clause a column alias and use that column alias in the ORDER BY clause as shown in the following query: SELECT | orderNumber | orderlinenumber | quantityOrdered * priceEach | QuantityOrdered * priceEach DESC Code language: SQL (Structured Query Language) ( sql ) It calculates the subtotal for each line item and sorts the result set based on the subtotal. The following query selects the order line items from the orderdetails table. See the following orderdetails table from the sample database. C) Using MySQL ORDER BY clause to sort a result set by an expression example In this example, the ORDER BY clause sorts the result set by the last name in descending order first and then sorts the sorted result set by the first name in ascending order to make the final result set. If you want to sort the customers by the last name in descending order and then by the first name in ascending order, you specify both DESC and ASC in these respective columns as follows: SELECTĬontactFirstname ASC Code language: SQL (Structured Query Language) ( sql ) Code language: plaintext ( plaintext ) B) Using MySQL ORDER BY clause to sort the result set by multiple columns example If you want to sort customers by the last name in descending order, you use the DESC after the contactLastname column in the ORDER BY clause as shown in the following query: SELECTĬontactLastname DESC Code language: SQL (Structured Query Language) ( sql ) SELECTĬontactLastname Code language: SQL (Structured Query Language) ( sql ) The following query uses the ORDER BY clause to sort the customers by their last names in ascending order. A) Using MySQL ORDER BY clause to sort the result set by one column example We’ll use the customers table from the sample database for the demonstration. When executing the SELECT statement with an ORDER BY clause, MySQL always evaluates the ORDER BY clause after the FROM and SELECT clauses: Note that the order of values in the column1 will not change in this step, only the order of values in the column2 changes. Then, sort the sorted result set by the values in the column2 in descending order.First, sort the result set by the values in the column1 in ascending order.It is possible to sort the result set by a column in ascending order and then by another column in descending order: ORDER BYĬolumn2 DESC Code language: SQL (Structured Query Language) ( sql ) In this case, the ORDER BY clause sorts the result set by column1 in ascending order first and sorts the sorted result set by column2 in ascending order. If you want to sort the result set by multiple columns, you specify a comma-separated list of columns in the ORDER BY clause: ORDER BYĬolumn2 Code language: SQL (Structured Query Language) ( sql ) Therefore, the following ORDER BY clauses are equivalent: ORDER BY column1 ASC Code language: SQL (Structured Query Language) ( sql )Īnd ORDER BY column1 Code language: SQL (Structured Query Language) ( sql ) This ORDER BY clause sorts the result set by the values in the column1 in ascending order: ORDER BY column1 ASC Code language: SQL (Structured Query Language) ( sql )Īnd this ORDER BY clause sorts the result set by the values in the column1 in descending order: ORDER BY column1 DESC Code language: SQL (Structured Query Language) ( sql )īy default, the ORDER BY clause uses ASC if you don’t explicitly specify any option. You use ASC to sort the result set in ascending order and DESC to sort the result set in descending order respectively. The ASC stands for ascending and the DESC stands for descending. In this syntax, you specify the one or more columns that you want to sort after the ORDER BY clause. Code language: SQL (Structured Query Language) ( sql ) The following illustrates the syntax of the ORDER BY clause: SELECT To sort the rows in the result set, you add the ORDER BY clause to the SELECT statement. When you use the SELECT statement to query data from a table, the order of rows in the result set is unspecified. Introduction to the MySQL ORDER BY clause Summary: in this tutorial, you will learn how to sort the rows in a result set using the MySQL ORDER BY clause.
0 Comments
Leave a Reply. |