SELECT with ORDER BY

          By default, the rows in the result set produced by a SELECT statement are returned by the server to the client in no particular order.  When a query is issued, the server is free to return the rows in any convenient order.  This order can be affected by factors such as the order in which the rows are actually stored in the table, or which indexes are used to process the query.  If the output rows need to be returned in a specific order, include an ORDER BY clause that indicates how to sort the results. There is no guarantee about the order in which the server returns rows, unless the order is specified.  To do so, add an ORDER BY clause to the statement that defines the sort order desired. Single Column Sorts The following example returns country names (in the Country table of the world database) alphabetically by Country Name; 




mysql> SELECT Name FROM Country ORDER BY Name; 


+------------------------------+ 
| Name                            | 
+------------------------------+ 
| Afghanistan                  | 
| Albania                         | 
| Algeria                          | 
| American Samoa        | 
| Andorra                        | 
| Angola                          | 
| Anguilla                        | 
| Antarctica                    | 
| Antigua and Barbuda | 
| Argentina                     | 
          ... 





Ordering the ORDER BY 

Ascending order is the default sort order in an ORDER BY  clause.  The order in which the results are ordered can be controlled with the keywords ASC (Ascending) or DESC (Descending) following the column names. Ascending means that the lowest value first. The statement below sorts the names of the countries in order by descending alphabetical order; 

mysql> SELECT Name FROM Country ORDER BY Name DESC; 

+--------------------------+ 

| Name                       | 

+--------------------------+ 

| Zimbabwe                | 

| Zambia                     | 

| Yugoslavia                | 

| Yemen                       | 

| Western Sahara       | 

| Wallis and Futuna    | 

| Virgin Islands, U.S.  | 

           ... 

Multiple Column Sorts 
Multiple columns can also be sorted simultaneously with differing sorting orders.  This is accomplished by separating the column names with a comma, as in the following example; 

mysql> SELECT Name, Continent FROM Country 

         -> ORDER BY Continent DESC, Name ASC; 

+------------------+---------------------+ 

| Name             | Continent           | 

+------------------+----------------------+ 

| Argentina        | South America | 

| Bolivia             | South America | 

| Brazil               | South America | 

| Chile                | South America | 

                 ... 

| Uzbekistan      | Asia                  | 

| Vietnam           | Asia                  | 

| Yemen             | Asia                   | 

+-------------------+---------------------+ 

239 rows in set (#.## sec) 

Note: In the above example, the Continent column is data type ENUM, therefore it is ordered by number of placement in list, not alphabetically.