General form of ORDER BY Clause - Firebird

The ORDER BY clause is used for sorting the output of a SELECT query and is valid for any SELECT statement that is capable of retrieving multiple rows for output. It is placed after all other clauses except a FOR UPDATE clause (if any) or an INTO clause (in a stored procedure).

The general form of the ORDER BY clause syntax is

Sorting Items

The comma-separated <list of sorting items> determines the vertical sort order of the rows. Sorting for each sorting item can be in ascending (ASC) or descending (DESC) order. Ascending order is the default and need not be specified.

Firebird 1.5 supports placement of NULLs, if present in the sorting item. The default, NULLS LAST, causes nulls to be sorted to the end and need not be specified. Specify NULLS FIRST to have nulls placed before all values.

When there are multiple sorting items, the horizontal position of items in the clause is significant—the sorting precedence is from left to right.

Columns

Sorting items are commonly columns. Indexed columns are sorted much faster than non-indexed columns. If a sort involves multiple columns in an unbroken left-to-right sequence, a compound index made up of the sorting items in the same left-to-right sequence and the appropriate sort direction (ASC/DESC) could speed up the sort dramatically.

In UNION and GROUP BY queries, the column used for sorting has to be present in the output list. In other queries, non-output columns and (in v.1.5) expressions on non-output columns are valid ordering criteria.

The statement in Example shows sorting by columns in its simplest form.

Simple Sorting by Columns

Using Table Aliases

When specifying an ordered set that specifies joining or a correlated subquery, you must provide fully qualified identifiers for all sorting items that are database columns or refer to database columns, using the table identifiers or aliases that were initialized in the FROM and JOIN clauses, as shown in Example.

Table Aliasing in the Ordering Clause

Expressions

From Firebird 1.5 onward, valid expressions are allowed as sort items, even if the expression is not output as a runtime column. You can sort sets on internal or external function expressions or correlated subqueried scalars. If the expression column you want to sort on is present in the output list, you cannot use its alias name as the ordering item. Either repeat the expression in the ORDER BY clause or use the column’s degree number (see the section “Degree Number”).

Ordering by an Expression Field

Alternatively, substituting the expression in the ordering clause with the degree number of the expression field:

If you need to sort by an expression in version 1.0.x, it is necessary to include the expression in the output list and use its degree number in the ORDER BY clause.

Sorting on Procedure or Function Expressions

A sort on values returned from a regular or external function or a stored procedure is just like any other sort: It will follow the regular ordinal rules for the data type of the returned field. It is your responsibility to specify sorting only on values that accord to some logical sequence. For an example of an inappropriate sequence, consider this:

... ORDER BY CAST(SALES_DATE AS VARCHAR(24))

Rows would be ordered in the alphabetical collation sequence of the characters, with no relationship to the dates on which the expression operated.

Degree Number

A special kind of expression that can be specified a sorting criterion is the degree number of the output column list. Degree number is the position of the column in the output, counting from left to right, starting at 1. Example respecifies the simple query in the next example, using degree numbers instead of column identifiers.

Example 23-4. Substituting Degree Numbers for Column Names

An ordering clause for sorting the output of a union query can use only degree numbers to refer to the ordering columns, as shown in Example.

Ordering UNION Sets

In v.1.0.x, ordering by degree number is the only way to use expressions as sort criteria, and the expression-derived column must be present in the output specification. In v.1.5, you have the option of using an expression as a sort item without including the expression item in the output set, as shown in Example.

Any Version

Firebird 1.5 and Higher

SELECT FULL_NAME FROM EMPLOYEE ORDER BY STRLEN(FULL_NAME) DESC;

Using the degree number is considered useful for saving typing and avoiding clutter when the output set involves joins, since all database columns named as ordering criteria must have fully qualified identifiers.

Sorting by degree number does not speed up the query; the engine recalculates expressions for the sorting operation.

Sort Direction

By default, sorts are performed in ascending order. To have the sort performed in descending order, include the keyword DESC, as shown in Example. If you need a descending sort, create a descending index for it. Firebird cannot “invert” an ascending index and use it for a descending sort, nor can it use a descending index for an ascending sort.

Descending Order

SELECT FIRST 10 * FROM DOCUMENT ORDER BY STRLEN(DESCRIPTION) DESC

Nulls Placement

By default, Firebird positions sort columns having null in the sort column at the bottom of the output set. From v.1.5 onward, you can use the NULLS FIRST keyword to specify that nulls be placed first, at the top of the set, as shown in Example.

Nulls Placement

SELECT * FROM EMPLOYEE ORDER BY PHONE_EXT NULLS FIRST

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics