SQL Reference Guide

ORDER BY

Description

The ORDER BY clause specifies the sorting of rows retrieved by the SELECT statement. FairCom DB SQL does not guarantee the sort order of rows unless the SELECT statement includes an ORDER BY clause.

Syntax

ORDER BY { expr | posn } [ ASC | DESC ]

[ , { expr | posn } [ASC | DESC] , ... ]

Notes

• Ascending order is the default ordering. The descending order will be used only if the keyword DESC is specified for that column.
• Each expr is an expression of one or more columns of the tables specified in the FROM clause of the SELECT statement. Each posn is a number identifying the column position of the columns being selected by the SELECT statement.
• The selected rows are ordered on the basis of the first expr or posn and if the values are the same then the second expr or posn is used in the ordering.
• The ORDER BY clause if specified should follow all other clauses of the SELECT statement.
• An ORDER BY clause can appear in a subquery, however, subqueries with a combination of TOP, GROUP BY and outer references remain unsupported.
• An ORDER BY clause can appear in a FOR UPDATE query.

  Note: This is a non-standard SQL feature as specified by SQL92. Also, full cursor update is not supported by FairCom DB SQL.

• A query expression followed by an optional ORDER BY clause can be specified. In such a case, if the query expression contains set operators, then the ORDER BY clause can specify column names or position from the first SELECT statement.

  For example:

-- Get a merged list of customers and suppliers

-- sorted by their name.

(SELECT name, street, state, zip

FROM customer

UNION

SELECT name, street, state, zip

FROM supplier)

ORDER BY 1 ;


(SELECT name, street, state, zip

FROM customer

UNION

SELECT name, street, state, zip

FROM supplier)

ORDER BY customer.name;

• In V11 and later, a query projecting an LVARBINARY column using an ORDER BY clause that requires sorting in memory may result in projecting null values. Prior to this change, LVARCHAR was the only supported long type. The logic has been updated to handle LVARBINARY.

Example

SELECT name, street, city, state, zip

FROM customer

ORDER BY name ;