By default, Omnidex will returned selected rows in the order returned by RDBMS or by how the data is indexed. The ORDER BY clause is used to return selected rows in sorted ascending or descending order. Multiple column_specs can be specified to cause a sort within the primary sort.
Depending on how a particular table is indexed, Omnidex will either perform a sort on the selected rows or use an Omnidex index.
ORDER BY [ column_spec | column_position ] [ ASC | DESC ]
[, column_spec | column_position ] [ ASC | DESC ]
The column_spec can be in the form of [database_name.][table_name.]column_name. The column_spec can also be a column alias specified in the select_list or the ordinal position of a column in the select_list.
Column Names
SELECT company, contact, phone FROM customers ORDER BY company
SELECT company, contact, phone FROM customers ORDER BY company, contact
SELECT company, contact, phone FROM customers ORDER BY state
By Ordinal Position
select trans_date, trans_amount from trans_table order by 2;
Column Alias
SELECT company CO, contact CT, phone PH FROM customers ORDER BY CO
SELECT company, contact, phone FROM customers ORDER BY CO, CT
The column_spec can be in the format of [database.][table.]column or table_alias.column.
select trans_date, trans_amount from trans_table order by db1.trans_table.trans_amount;
Column_spec can also be prefixed with an alias name of the table's column or an alias name of an expression.
select trans_date, trans_amount - discount NetAmount from trans_table order by NetAmount;
Ascending (ASC), Descending (DESC) DESC (Descending) sorts will NOT be optimized, meaning they may increase the overall elapsed query time. ASC (Ascending) sorts may be optimized, depending on the index installation. Ascending is the default if neither is specified.
SELECT company, contact, phone FROM customers ORDER BY company asc;
SELECT customer_no, count(distinct order_no) FROM orders GROUP BY customer_no
ORDER BY 2 DESC, 1 ASC;