Previous Section  < Day Day Up >  Next Section

5.3 Using ORDER BY to Sort Query Results

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 a factor 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 use a table t that has the following contents (id is numeric, last_name and first_name are strings, and d contains dates):







mysql> SELECT id, last_name, first_name, d FROM t;

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

| id   | last_name | first_name | d          |

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

|    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 using the values in that column:

    




    
mysql> SELECT id, last_name, first_name, d FROM t

    -> ORDER BY d;

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

| id   | last_name | first_name | d          |

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

|    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:

    




    
mysql> SELECT id, last_name, first_name, d FROM t

    -> ORDER BY last_name, first_name;

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

| id   | last_name | first_name | d          |

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

|    3 | Brown     | Betty      | 1971-07-12 |

|    1 | Brown     | Bill       | 1972-10-14 |

|    4 | Larsson   | Selma      | 1968-05-29 |

|    2 | Larsson   | Sven       | 1965-01-03 |

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

    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:

    




    
mysql> SELECT id, last_name, first_name, d FROM t

    -> ORDER BY id DESC;

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

| id   | last_name | first_name | d          |

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

|    4 | Larsson   | Selma      | 1968-05-29 |

|    3 | Brown     | Betty      | 1971-07-12 |

|    2 | Larsson   | Sven       | 1965-01-03 |

|    1 | Brown     | Bill       | 1972-10-14 |

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

    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 can refer to that 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;

    One caution against specifying columns by position is that this syntax has been removed from the SQL Standard (in SQL:1999) and should be considered obsolete. Application developers should keep this in mind and 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 or by its column position (although the latter isn't recommended). The following queries each sort the output rows by month of the year:

    




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

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

SELECT id, last_name, first_name, MONTH(d) FROM t ORDER BY 3;

  • You can sort output using 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:

    




    
mysql> SELECT id, last_name, first_name, MONTHNAME(d) FROM t

    -> ORDER BY MONTH(d);

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

| id   | last_name | first_name | MONTHNAME(d) |

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

|    2 | Larsson   | Sven       | January      |

|    4 | Larsson   | Selma      | May          |

|    3 | Brown     | Betty      | July         |

|    1 | Brown     | Bill       | October      |

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

  • 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. (See section 5.4, "Limiting a Selection Using LIMIT.")

  • ORDER BY may be used with DELETE or UPDATE to force rows to be deleted or updated in a certain order. These uses of ORDER BY are covered in Chapter 7, "Update Statements."

5.3.1 The Natural Sort Order of Column Types

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

  • Numeric columns sort in ascending numeric order by default, or descending order if DESC is specified.

  • DECIMAL columns are numeric, so they sort numerically, even though DECIMAL values are stored in string form.

  • Date and time columns sort in ascending temporal order by default, with oldest values first and most recent values last. The order is reversed if DESC is specified.

  • The sort order for string columns other than ENUM and SET depends on whether the column contains binary or nonbinary values. Binary strings sort based on the numeric values of the bytes that make up the strings. This means that the sort order is case sensitive. Nonbinary strings sort in lexical order, which is defined by the collating sequence of the server's character set. Typically, this means that nonbinary string sorts aren't case sensitive. For example, assume that a table t has a CHAR column c that contains the following values:

    




    
mysql> SELECT c FROM t;

+------+

| c    |

+------+

| D    |

| a    |

| c    |

| B    |

+------+

    A CHAR column is nonbinary by default, so its contents sort lexically without regard to lettercase:

    




    
mysql> SELECT c FROM t ORDER BY c;

+------+

| c    |

+------+

| a    |

| B    |

| c    |

| D    |

+------+

    You can force a string column sort to be case sensitive by using the BINARY keyword:

    




    
mysql> SELECT c FROM t ORDER BY BINARY c;

+------+

| c    |

+------+

| B    |

| D    |

| a    |

| c    |

+------+

  • 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 is 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 sort using the string values, 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 3 (1+2), 7 (1+2+8), 5 (1+4), and 6 (2+4). An ORDER BY on the column sorts using these numeric values:

    




    
mysql> SELECT hue FROM t ORDER BY hue;

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

| hue            |

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

| red,green      |

| red,blue       |

| green,blue     |

| red,green,blue |

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

  • NULL values in a column sort together at one end or the other of the sort order. For MySQL, NULL values sort at the beginning for ascending sorts and at the end for descending sorts. (However, MySQL does not fully implement this behavior until version 4.0.11. In most earlier MySQL 4 releases, NULL values sort at the beginning, regardless of sort direction.)

    Previous Section  < Day Day Up >  Next Section