1. SQL Beginner's Guide
  2. Working with complex data
  3. Using ORDER BY to Sort Results

Using ORDER BY to Sort Results

To sort the results from a SELECT statement using a field that is not the table's primary key, you must first create an index for the column of choice. For example, for the next table, to query based on a Timestamp and sort the results in descending order by the timestamp, create an index: 

sql-> SELECT id, firstname, lastname, lastLogin FROM Persons;
 +----+-----------+----------+--------------------------+
 | id | firstname | lastname |        lastLogin         |
 +----+-----------+----------+--------------------------+
 |  3 | John      | Morgan   | 2016-11-29T08:21:35.4971 |
 |  4 | Peter     | Smith    | 2016-10-19T09:18:05.5555 |
 |  2 | John      | Anderson | 2016-11-28T13:01:11.2088 |
 |  5 | Dana      | Scully   | 2016-11-08T09:16:46.3929 |
 |  1 | David     | Morrison | 2016-10-29T18:43:59.8319 |
 +----+-----------+----------+--------------------------+

5 rows returned

sql-> CREATE INDEX tsidx1 on Persons (lastLogin);
Statement completed successfully
sql-> SELECT id, firstname, lastname, lastLogin 
FROM Persons ORDER BY lastLogin DESC;
 +----+-----------+----------+--------------------------+
 | id | firstname | lastname |        lastLogin         |
 +----+-----------+----------+--------------------------+
 |  3 | John      | Morgan   | 2016-11-29T08:21:35.4971 |
 |  2 | John      | Anderson | 2016-11-28T13:01:11.2088 |
 |  5 | Dana      | Scully   | 2016-11-08T09:16:46.3929 |
 |  1 | David     | Morrison | 2016-10-29T18:43:59.8319 |
 |  4 | Peter     | Smith    | 2016-10-19T09:18:05.5555 |
 +----+-----------+----------+--------------------------+

5 rows returned

SQL for Oracle NoSQL Database can also sort query results by the values of nested records. To do so, create an index of the nested field (or fields). For example, you can create an index of address.state from the Persons table, and then order by state: 

sql-> CREATE INDEX indx1 on Persons (address.state);
Statement completed successfully
sql-> SELECT id, $p.address.state FROM
Persons $p ORDER BY $p.address.state;
 +----+-------+
 | id | state |
 +----+-------+
 |  3 | FL    |
 |  4 | MA    |
 |  5 | NJ    |
 |  1 | TN    |
 |  2 | WI    |
 +----+-------+

5 rows returned

To learn more about indexes, see Working With Indexes.