Ordering Results

You can order the result by a primary key column or a non-primary key column using the ORDER BY clause.

Note

You can use ordering 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, you need to first create an index. To create an index and then order by lastname:

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

The idx2 index can also be used to order by age only (but not 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 done in ascending order. To sort in descending order use the DESC keyword in the ORDER BY:

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