ORDER BY clause

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > SELECT statement  >

ORDER BY clause

Previous pageReturn to chapter overviewNext page

The ORDER BY clause allows you to specify the columns, or expressions, by which a relational (non-streaming) query is sorted. If you do not specify an ORDER BY clause, the order of the rows is unspecified.

ORDER BY is also supported for streaming queries (see Streaming Order By) and, by using a new SQLstream keyword, can be used to time-sort rows in a time-based window. See the topic T-sorting Stream Input in this guide for more details.

Syntax Chart for the ORDER BY Clause

To see where this clause fits, see the SELECT chart in the topic SELECT statement in this guide.

select_06_order_by_w_expression

Note: For the use and effect of the t_sort_clause, see the topic T-sorting Stream Input in this guide.

The ORDER BY clause is evaluated after FROM, WHERE, GROUP BY, HAVING, and SELECT clauses. As a result, expressions that are filtered away by a GROUP BY or SELECT DISTINCT operation cannot be sorted on.

For example, it is illegal to write

SELECT DISTINCT deptno

FROM emp

ORDER BY gender

 

because for a given value of deptno there is no single well-defined value of gender.

If an expression has been given a column alias in the SELECT clause, you can refer to it by that alias.

If the expression is an integer literal n, it means the nth entry in the SELECT clause.

For example, the following three queries are equivalent:

  SELECT orderId, unitPrice * quantity AS total

  FROM Orders

  ORDER BY 2;

  SELECT orderId, unitPrice * quantity AS total

  FROM Orders

  ORDER BY total;

  SELECT orderId, unitPrice * quantity AS total

  FROM Orders

  ORDER BY unitPrice * quantity;

Streaming Order By

A streaming query can use ORDER BY if its leading expression is time-based and monotonic. For example, a streaming query whose leading expression is based on the ROWTIME column can use ORDER BY to do the following operations:

Sort the results of a streaming GROUP BY.

Sort a batch of rows arriving within a fixed time window of a stream.

Perform streaming ORDER BY on windowed-joins.

The "time-based and monotonic" requirement on the leading expression means that the query

SELECT STREAM DISTINCT ticker FROM trades ORDER BY ticker

 

will fail, but the query

SELECT STREAM DISTINCT rowtime, ticker FROM trades ORDER BY ROWTIME, ticker

 

will succeed.

Streaming ORDER BY sorts rows using SQL-2008 compliant syntax for the ORDER BY clause. It can be combined with a UNION ALL statement, and can sort on expressions, such as:

SELECT STREAM x, y FROM t1

UNION ALL

SELECT STREAM a, b FROM t2 ORDER BY ROWTIME, MOD(x, 5)

 

 

The ORDER BY clause can specify ascending or descending sort order, and can use column ordinals, as well as ordinals specifying (referring to) the position of items in the select list.

Streaming ORDER BY SQL Declarations

The streaming ORDER BY clause includes the following functional attributes:

Gathers rows until the monotonically increasing expression in streaming ORDER BY clause does not change.

Does not require streaming GROUP BY clause in the same statement.

Can use any column with a basic SQL data type of TIMESTAMP, DATE, DECIMAL, INTEGER, FLOAT, CHAR, VARCHAR.

Does not require that columns/expressions in the ORDER BY clause be present in the SELECT list of the statement.

Applies all the standard SQL validation rules for ORDER BY clause.

Streaming ORDER BY can include the pseudo-column ROWTIME. As a pseudo-column, ROWTIME behaves as a stream column, but is not stored in the stream.

Note: Unadorned ROWTIME (not qualified with a stream name) only be specified in the SELECT list or the ORDER BY clause.

The following query is an example of streaming ORDER BY:

SELECT STREAM state, city, SUM(amount)

FROM orders

GROUP BY FLOOR(ROWTIME TO HOUR), state, city

ORDER BY FLOOR(ROWTIME TO HOUR), state, SUM(amount);