ORDER BY

ORDER BY

The ORDER BY clause is used to sort the result set by one or more output expressions.

ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]

Each expression can consist of output columns or you can select the sequence number of an output column by position.

The ORDER BY clause is executed after the GROUP BY or HAVING clause and before the OFFSET, LIMIT, or FETCH FIRST clause.

According to the SQL specification, the ORDER BY clause affects only the row order of the query result that contains this clause. HetuEngine complies with this specification and deletes redundant usage of the clause to avoid negative impact on performance.

For example, when an INSERT statement is executed, the ORDER BY clause does not affect the inserted data. It is a redundant operation and adversely affects the overall performance of the INSERT statement. Therefore, HetuEngine skips this ORDER BY clause.

• ORDER BY applies only to the SELECT clause.

  INSERT INTO some_table
  SELECT * FROM another_table
  ORDER BY field;

• The example of ORDER BY redundancy is nested query, which does not affect the result of the entire statement.

  SELECT *
  FROM some_table
  JOIN (SELECT * FROM another_table ORDER BY field) u
  ON some_table.key = u.key;