Omnidex SQL: SELECT ORDER BY Clause

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.

  ORDER BY [  column_spec | column_position ] [ ASC | DESC ] 
           [, column_spec | column_position ] [ ASC | DESC ]

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;