This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:statements:select:order_by [2010/07/03 21:30] tdo |
dev:sql:statements:select:order_by [2012/10/26 15:00] (current) |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| ====== Omnidex SQL: SELECT ORDER BY Clause ====== | ====== Omnidex SQL: SELECT ORDER BY Clause ====== | ||
| {{page>:sql_bar&nofooter&noeditbtn}} | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | ^See Also:^[[dev:sql:statements:select | SELECT statement ]]^FROM clause^WHERE clause^GROUP BY clause^ | + | ^See Also:^[[dev:sql:statements:select:home | SELECT statement ]]^[[dev:sql:statements:select:from | FROM clause]]^[[dev:sql:statements:select:where |WHERE clause ]]^[[dev:sql:statements:select:group_by| GROUP BY clause]]^ |
| ===== Description ===== | ===== Description ===== | ||
| - | ==== ORDER BY Clause ==== | + | 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. |
| - | The ORDER BY clause returns selected rows in ascending or descending order by the specified column_spec or the ordinal position of a column_spec in the column list. | + | Depending on how a particular table is indexed, Omnidex will either perform a sort on the selected rows or use an Omnidex index. |
| + | ===== Syntax ===== | ||
| - | select trans_date, trans_amount from trans_table order by trans_date; | + | <code> |
| + | ORDER BY [ column_spec | column_position ] [ ASC | DESC ] | ||
| + | [, column_spec | column_position ] [ ASC | DESC ] | ||
| + | </code> | ||
| + | ===== Discussion ===== | ||
| + | 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//. | ||
| + | ===== Examples ===== | ||
| + | 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; | 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. | The column_spec can be in the format of [database.][table.]column or table_alias.column. | ||
| Line 22: | Line 44: | ||
| select trans_date, trans_amount - discount NetAmount from trans_table order by NetAmount; | select trans_date, trans_amount - discount NetAmount from trans_table order by NetAmount; | ||
| - | The ASC/DESC modifier can be used to return the data in ascending or descending order with ascending order being the default. | + | 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 trans_date, trans_amount from trans_table order by trans_date desc; | + | SELECT customer_no, count(distinct order_no) FROM orders GROUP BY customer_no |
| + | ORDER BY 2 DESC, 1 ASC; | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||