ORDER BY Examples

The following examples illustrate the use of ORDER BY for delegation. Any null values will be returned at the bottom of the results. If the statement included an ORDER BY DESC, null values would appear at the top.

Example 1:

SELECT city, pop FROM table WHERE obj Within <geometry> ORDER BY pop

This example will be delegated to the data source provider, assuming it supports ORDER BY an Within. Spectrum Spatial does not need to process this query.

Example 2:

SELECT city, pop FROM table WHERE obj Within <geometry> ORDER BY pop, city

This example contains two ORDER BY clauses. According to the rules for ORDER BY only one can be delegated (the leftmost). In this case Spectrum Spatial will do some of the processing. The delegated query looks like:

SELECT city, pop FROM table WHERE obj Within <geometry> ORDER BY pop

Spectrum Spatial handles the ORDER BY city using the results of the delegated query.

SELECT city, pop FROM table WHERE obj Within <geometry> ORDER BY city
Example 3:
This example includes a function. According to delegation rules, functions cannot be delegated.
SELECT UPPER(city) FROM table WHERE city='Austin' ORDER BY hhinc