How to Sort a MySQL Query Using ORDER by

Rows from a MySQL SELECT query are returned in an arbitrary or unpredictable order. Since the return has more to do with internal hashing and search algorithms than it does with your data, the order of the rows is not usually useful to a programmer. To return the rows in a meaningful order, you can use the ORDER BY clause of the SELECT command to sort them.

Difficulty: Moderate

Instructions

1. Form the basic SELECT command to return the rows in which you are interested. At this point, the order in which the rows are returned is not of importance. This example queries a customers database to get a list of customers with negative balances. Example:
SELECT * FROM customers WHERE balance < 0.0;

2. Add an ORDER BY clause. ORDER BY will tell the MySQL server to sort the rows by a column. For example, if you want to see all outstanding accounts in order of their balance, you can add "ORDER BY balance" to the end of the query. Example:
SELECT * FROM customers WHERE balance < 0.0 ORDER BY balance;

3. Define in which direction to sort, as the order of the returned rows may not yet be meaningful. Rows can be returned in ascending or descending order.

4. Use ASC or DESC. Using ASC will sort the data so that you see the smallest number first. Using DESC will sort the data so that you see the largest number first. In this query, you are looking for customers with the largest negative balance first. ORDER BY will return the arrays with the greatest negative number (the smallest number) at the top. Example:
SELECT * FROM customers WHERE balance < 0.0 ORDER BY balance ASC;

5. Sort by multiple columns, as sometimes you'll come across large groups rows that were sorted on the same number. For example, if there are 100 customers in your database with a balance of 0, the customers will appear in arbitrary order. It's best to have at least two sorting criteria for situations like this.

6. Use multiple sorting criteria separated by commas. Here, customers are shown in alphabetical order by name after they are sorted by balance. Example:
SELECT * FROM customers WHERE balance < 0.0 ORDER BY balance,name ASC;

Tips & Warnings

• By itself, ORDER BY is not a command. It is a clause, or additional component of, the SELECT command.