Ordering Results

Use the ORDER BY clause to order the results by a primary key column or a non-primary key column.

Note:

You can use ORDER BY only if you are selecting by the table's primary key, or if there is an index that sorts the table's rows in the desired order.

To order by using a primary key column (id), specify the sort column in the ORDER BY clause:

sql-> SELECT id, lastname FROM Users ORDER BY id;
 +----+----------+
 | id | lastname |
 +----+----------+
 |  1 | Morrison |
 |  2 | Anderson |
 |  3 | Morgan   |
 |  4 | Smith    |
 |  5 | Scully   |
 +----+----------+

5 rows returned 

To order by a non-primary key column, first create an index on the column of interest. For example, to use column lastname for ordering, create an index on that column, before using it in your ORDER BY clause:

sql-> CREATE INDEX idx1 on Users(lastname);
Statement completed successfully
sql-> SELECT id, lastname FROM Users ORDER BY lastname;
 +----+----------+
 | id | lastname |
 +----+----------+
 |  2 | Anderson |
 |  3 | Morgan   |
 |  1 | Morrison |
 |  5 | Scully   |
 |  4 | Smith    |
 +----+----------+

5 rows returned 

Using this example data, you can order by more than one column if you create an index on the columns. (If our table had used more than one column for its primary key, then you can order by multiple columns using the primary keys.) For example, to order users by age and income.

sql-> CREATE INDEX idx2 on Users(age, income);
Statement completed successfully
sql-> SELECT id, lastname, age, income FROM Users ORDER BY age, income;
 +----+----------+-----+--------+
 | id | lastname | age | income |
 +----+----------+-----+--------+
 |  1 | Morrison |  25 | 100000 |
 |  2 | Anderson |  35 | 100000 |
 |  4 | Smith    |  38 |  80000 |
 |  3 | Morgan   |  38 |   NULL |
 |  5 | Scully   |  47 | 400000 |
 +----+----------+-----+--------+

5 rows returned 

Creating a single index from two columns in the order you use them (age, income in this example), has some limits. The first column name (age) becomes the main sort item for the new index. You can use idx2 index to order by age only, but neither by income only, nor by income first and age second.

sql-> SELECT id, lastname, age from Users ORDER BY age;
 +----+----------+-----+
 | id | lastname | age |
 +----+----------+-----+
 |  1 | Morrison |  25 |
 |  2 | Anderson |  35 |
 |  4 | Smith    |  38 |
 |  3 | Morgan   |  38 |
 |  5 | Scully   |  47 |
 +----+----------+-----+

5 rows returned 

To learn more about indexes see Working With Indexes.

By default, sorting is performed in ascending order. To sort in descending order use the DESC keyword in the ORDER BY clause:

sql-> SELECT id, lastname FROM Users ORDER BY id DESC;
 +----+----------+
 | id | lastname |
 +----+----------+
 |  5 | Scully   |
 |  4 | Smith    |
 |  3 | Morgan   |
 |  2 | Anderson |
 |  1 | Morrison |
 +----+----------+

5 rows returned