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
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.
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
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.
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.
Firebird 1.5 and HigherSELECT 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.
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 OrderSELECT FIRST 10 * FROM DOCUMENT ORDER BY STRLEN(DESCRIPTION) DESC
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 PlacementSELECT * FROM EMPLOYEE ORDER BY PHONE_EXT NULLS FIRST
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.