Home / SQL Statements / Select Statement / Question No: 215

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 you issue a query, 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 rows are actually stored in the table or which indexes are used to process the query. If you require output rows to be returned in a specific order, include an ORDER BY clause that indicates how to sort the results.

The examples in this section demonstrate ORDER BY using a table t that has the following contents (id is numeric, last_name and first_name are strings, and birth contains dates):

mysql> SELECT id, last_name, first_name, birth FROM t;
+------+-----------+------------+------------+
| id   | last_name | first_name | birth      |
+------+-----------+------------+------------+
|    1 | Brown     | Bill       | 1972-10-14 |
|    2 | Larsson   | Sven       | 1965-01-03 |
|    3 | Brown     | Betty      | 1971-07-12 |
|    4 | Larsson   | Selma      | 1968-05-29 |
+------+-----------+------------+------------+

ORDER BY provides a great deal of flexibility for sorting result sets. It has the following characteristics:

• You can name one or more columns, separated by commas, to use for sorting. With a single sort column, rows are sorted based on the values in that column:

  mysql> SELECT id, last_name, first_name, birth FROM t ORDER BY birth;
  +------+-----------+------------+------------+
  | id   | last_name | first_name | birth      |
  +------+-----------+------------+------------+
  |    2 | Larsson   | Sven       | 1965-01-03 |
  |    4 | Larsson   | Selma      | 1968-05-29 |
  |    3 | Brown     | Betty      | 1971-07-12 |
  |    1 | Brown     | Bill       | 1972-10-14 |
  +------+-----------+------------+------------+
  

  If there are additional sort columns, rows with the same value in the first sort column are sorted together, and are then further sorted using the values in the second and remaining sort columns. The following query sorts the Browns before the Larssons, and then within each group of rows with the same last name, sorts them by first name:

  SELECT id, last_name, first_name, birth FROM t ORDER BY last_name, first_name;

• By default, ORDER BY sorts values in ascending order (smallest to largest). Any sort column may be followed with ASC if you want to specify ascending order explicitly. These ORDER BY clauses are equivalent:

  ORDER BY last_name, first_name
  ORDER BY last_name ASC, first_name ASC

  To sort values in descending order (largest to smallest), follow the sort column name with DESC:

  SELECT id, last_name, first_name, birth FROM t ORDER BY id DESC;

  When you name a column followed by ASC or DESC, the sort direction specifier applies only to that column. It doesn't affect sort direction for any other columns listed in the ORDER BY clause.

• ORDER BY typically refers to table columns by name:

  SELECT last_name, first_name FROM t ORDER BY last_name, first_name;

  However, it's possible to refer to columns in other ways. If a column is given an alias in the output column list, you should refer to that column in the ORDER BY column by its alias:

  SELECT last_name AS last, first_name AS first FROM t ORDER BY last, first;

  Or you can specify a number corresponding to the column's position in the column output list (1 for the first output column, 2 for the second, and so forth):

  SELECT last_name, first_name FROM t ORDER BY 1, 2;

  However, the syntax for specifying columns by position has been removed from the SQL Standard (in SQL:1999) and is obsolete. Application developers should consider using one of the other column specification methods.

• It's possible to perform a sort using an expression result. If the expression appears in the output column list, you can use it for sorting by repeating it in the ORDER BY clause. Alternatively, you can refer to the expression by an alias given to it. The following queries each sort the output rows by month of the year:

  SELECT id, last_name, first_name, MONTH(birth) FROM t ORDER BY MONTH(birth);
  SELECT id, last_name, first_name, MONTH(birth) AS m FROM t ORDER BY m;

• Output sorting can be based on values that don't appear in the output at all. The following statement displays month names in the output, but sorts the rows using the numeric month value:

  SELECT id, last_name, first_name, MONTHNAME(birth) FROM t ORDER BY MONTH(birth);

• ORDER BY doesn't require the sorted columns to be indexed, although a query might run faster if such an index does exist.
• ORDER BY is useful together with LIMIT for selecting a particular section of a set of sorted rows.
• ORDER BY can be used with DELETE or UPDATE to force rows to be deleted or updated in a certain order.

The Natural Sort Order of Data Types

Each type of data managed by MySQL has its own natural sort order. For the most part, these orders are fairly intuitive. The rules for string sorting are the most complex because they depend on whether the strings are non-binary, binary, or come from ENUM or SET columns.

• A numeric column sorts in ascending numeric order by default, or descending order if DESC is specified.
• A temporal column sorts in ascending time order by default, with oldest values first and most recent values last. The order is reversed if DESC is specified.
• The sort order for a string column that has a data type other than ENUM or SET depends on whether the column contains non-binary or binary values. Non-binary strings sort in the order defined by their collation. This order can be case sensitive or not, depending on the collation. Binary strings sort based on the numeric values of the bytes contained in the strings. For example, assume that a table t has a CHAR column c that has the latin1 character set and that contains the following values:

  mysql> SELECT c FROM t;
  +------+
  | c    |
  +------+
  | a    |
  | A    |
  | B    |
  | A    |
  | b    |
  | a    |
  +------+
  

  A CHAR column is non-binary, so its contents sort according to the column's collation. If the collation is not case sensitive, values sort lexically without regard to lettercase:

  mysql> SELECT c FROM t ORDER BY c;
  +------+
  | c    |
  +------+
  | a    |
  | A    |
  | A    |
  | a    |
  | B    |
  | b    |
  +------+
  

  Notice that the results come out in letter order, but the rows for a given letter are not further sorted by lettercase.

  If the collation is case sensitive, lettercase becomes significant. You can force a string column sort to be case sensitive by using the COLLATE operator with a case-sensitive collation:

  mysql> SELECT c FROM t ORDER BY c COLLATE latin1_general_cs;
  +------+
  | c    |
  +------+
  | A    |
  | A    |
  | a    |
  | a    |
  | B    |
  | b    |
  +------+
  

  If the collation is binary, numeric character values are the determining factor:

  mysql> SELECT c FROM t ORDER BY c COLLATE latin1_bin;
  +------+
  | c    |
  +------+
  | A    |
  | A    |
  | B    |
  | a    |
  | a    |
  | b    |
  +------+
  

• The sort order for members of an ENUM or SET column is based on their internal numeric values. These values correspond to the order in which the enumeration or set members are listed in the column definition. Suppose that a table t contains a column mon that is an ENUM listing abbreviations for months of the year:

  CREATE TABLE t (mon ENUM('Jan','Feb','Mar','Apr','May','Jun', 'Jul','Aug','Sep','Oct','Nov','Dec'));
  

  Assume that table t has 12 rows, one for each of the possible enumeration values. When you sort this column, the values come out in month-of-year order:

  mysql> SELECT mon FROM t ORDER BY mon;
  +------+
  | mon  |
  +------+
  | Jan  |
  | Feb  |
  | Mar  |
  | Apr  |
  | May  |
  | Jun  |
  | Jul  |
  | Aug  |
  | Sep  |
  | Oct  |
  | Nov  |
  | Dec  |
  +------+
  

  This occurs because 'Jan' through 'Dec' are assigned internal values 1 through 12 based on their order in the column definition, and those values determine the sort order. To produce a lexical string sort instead, use CAST() to convert the enumeration values to CHAR values:

  mysql> SELECT mon FROM t ORDER BY CAST(mon AS CHAR);
  +------+
  | mon  |
  +------+
  | Apr  |
  | Aug  |
  | Dec  |
  | Feb  |
  | Jan  |
  | Jul  |
  | Jun  |
  | Mar  |
  | May  |
  | Nov  |
  | Oct  |
  | Sep  |
  +------+
  

  SET columns also sort using the internal values of the set's legal members. The ordering is more complex than with ENUM because values may consist of multiple SET members. For example, the following SET column contains three members:

  CREATE TABLE t (hue SET('red','green','blue'));

  Assume that t contains the following rows:

  mysql> SELECT hue FROM t;
  +----------------+
  | hue            |
  +----------------+
  | red,green      |
  | red,green,blue |
  | red,blue       |
  | green,blue     |
  +----------------+
  

  The SET members 'red', 'green', and 'blue' have internal values of 1, 2, and 4, respectively. Thus, the rows of the table have internal numeric values of 1+2 = 3, 1+2+4 = 7, 1+4 = 5, and 2+4 = 6. An ORDER BY on the column sorts using those numeric values:

  mysql> SELECT hue FROM t ORDER BY hue;
  +----------------+
  | hue            |
  +----------------+
  | red,green      |
  | red,blue       |
  | green,blue     |
  | red,green,blue |
  +----------------+
  

  As with ENUM, SET values can be sorted lexically by using CAST() to convert them to strings:

  mysql> SELECT hue FROM t ORDER BY CAST(hue AS CHAR);
  +----------------+
  | hue            |
  +----------------+
  | green,blue     |
  | red,blue       |
  | red,green      |
  | red,green,blue |
  +----------------+
  

• NULL values in a column sort together at the beginning for ascending sorts and at the end for descending sorts.