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:57] tdo |
dev:sql:statements:select:order_by [2016/06/28 22:38] (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:home | 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 ===== | ||
| - | The ORDER BY clause is used to return selected rows in ascending or descending order. | + | 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. | ||
| ===== Syntax ===== | ===== Syntax ===== | ||
| Line 13: | Line 14: | ||
| [, column_spec | column_position ] [ ASC | DESC ] | [, column_spec | column_position ] [ ASC | DESC ] | ||
| </code> | </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 ===== | ===== Examples ===== | ||
| - | select trans_date, trans_amount from trans_table order by trans_date; | + | 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; | ||
| Line 33: | 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}} | ||