Orders multiple rows produced simultaneously by a join operation in a Query Statement, Database statement, or Remote Procedure statement.
The optional ORDER BY clause orders multiple rows that are produced during one execution of a query. This clause is typically used with joins involving two or more data sources, in which a single row arriving in one of the query's data sources often results in multiple rows of output from the query. In cases where multiple rows are produced, the rows are first ordered according to the values of the first column reference specified in the clause. Rows with duplicate values in this column are further ordered by the second and subsequent column references, if any are specified. Each additional column reference further refines the ordering process.
You can specify DESCENDING (or DESC) with each column reference. These control whether ordering proceeds in ascending (the default) or descending order of values for each specified column.
When specifying a grouping with PER, ordering is performed for each unique combination of values in the specified columns. Specifying UNGROUPED instead of a PER clause to make the default behavior explicit has no effect. Note that any GROUP BY clause in the statement does not apply to the ORDER BY clause.
The ORDER BY clause reorders rows only within sets of rows produced simultaneously by a join operation. It does not reorder rows that have the same timestamp for other reasons and it never reorders rows with different timestamps.
The following example orders the output from the join by ascending values in the Temp.Location column. If multiple rows have the same value in Location, they are further ordered by the Wind.Windspeed column, in descending order:
INSERT INTO OutStream
SELECT Temp.Location, Temp.Temperature, Wind.WindSpeed,
AVG(Temp.Temperature)
FROM Temp KEEP 1 DAY, Wind KEEP 1 DAY
WHERE Temp.Location = Wind.Location
GROUP BY Temp.Location
ORDER BY Temp.Location, Wind.Windspeed DESC;