Use the ORDER BY Clause to Arrange Values

Using the Relational Data Framework › Use the ORDER BY Clause to Arrange Values

Use the ORDER BY Clause to Arrange Values

When you select values from a table without specifying detailed search criteria (as you would with the WHERE clause), you can get a random listing of elements that are not in any particular order. If you have a large table, it can be difficult to locate the value you are after. The ORDER BY clause allows you to order the rows according to the values in one or more of the columns.

Review the following tips for using the ORDER BY clause:

The data type of the column specified in the ORDER BY clause determines how the rows are ordered. For example, if the SELECT statement in the example had enacted the ORDER BY clause using the USER_ID column, the rows would have been arranged in alphabetical order, using the first characters found in the USER_ID column of each row.
You can also arrange the values in descending order by using the DESC operator. This causes the values to be ordered with the largest value first, descending to the lowest value.
The NULL value is higher than all other values for every data type.
If the ORDER BY clause specifies a column that contains duplicate values, then rows that have the same ORDER BY value are arranged in random order.

To arrange values using the ORDER BY clause

The following statement selects all rows from the APPLICATIONS table:
```
OPSQL SELECT * FROM APPLICATIONS
```
This statement would retrieve all of the rows of the table. If the table happened to contain hundreds of entries, you might have a hard time locating a particular user.
Include the ORDER BY clause to order the listing by update time, starting with the oldest update:
```
OPSQL SELECT * FROM APPLICATIONS ORDER BY UPDATE ASC
```
Produces this result:

APPL_ID	USER_ID	UPDATE	STATUS
APPL5	TSOUSR8	2009-01-31	DOWN
APPL2	TSOUSR2	2009-02-05	DOWN
APPL1	TSOUSR1	2009-02-13	UP
APPL10	TSOUSR22	2009-03-04	UP

The rows are now arranged by ascending values in the UPDATE column. The ASC operator specified ascending order, which caused it to start with the lowest value and increase.

The data type of the column specified in the ORDER BY clause determines how the rows are ordered. For example, if the SELECT statement in the example had enacted the ORDER BY clause using the USER_ID column, the rows would have been arranged in alphabetical order, using the first characters found in the USER_ID column of each row.

You can also arrange the values in descending order by using the DESC operator. This causes the values to be ordered with the largest value first, descending to the lowest value.

The NULL value is higher than all other values for every data type.

If the ORDER BY clause specifies a column that contains duplicate values, then rows that have the same ORDER BY value are arranged in random order.