Differences

This shows you the differences between two versions of the page.

--- dev:sql:statements:select:home [2010/07/02 09:29]
tdo
+++ dev:sql:statements:select:home [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Statement: SELECT ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Statement: SELECT ======
+^See Also:^[[dev:sql:statements:select:from |FROM clause]]^[[dev:sql:statements:select:where | WHERE clause ]]^[[dev:sql:statements:select:group_by | GROUP BY clause]]^[[dev:sql:statements:select:order_by | ORDER BY clause ]]^
 ===== Description =====
 The Omnidex SQL Select statement is the primary statement used to query records on Omnidex enhanced data.
@@ Line 14: / Line 15: @@
       [ < TOP n [ SKIP n ] | EVERY n [ SKIP n ] | RANDOM n | RANDOM (n, seed) > ]
       [ SAMPLE(‘control_break’ [, ’qualifier’]) ]
-      [ DISTINCT ]
+      [ < DISTINCT column_spec | ALL >]
       select_list
       FROM data_source
-      [ WHERE criteria_list ]
+      [ WHERE criteria_specification ]
-      [ GROUP BY group_by_list ]
+      [ GROUP BY group_by_specification ]
-          [ HAVING having_list ]
+          [ HAVING having_specification ]
-      [ ORDER BY order_by_list ]
+      [ ORDER BY order_by_specifcation ]
       [ < UNION [ALL] | INTERSECT | EXCEPT > select_statement ]
       [ ON [ CURSOR ] cursor_no ] | [ INSTANCE instance_no ]
       [ WITH option_list ]
 </code>
-==== Expression_list syntax: ====
+==== Select_list syntax: ====
 <code>
-  select_list
-         [ < TOP (n)
-             [ SKIP(n) | EVERY(n) | RANDOM (n [, seed] ) ] ]
-         [ < DISTINCT column_spec | ALL >]
-         [ SAMPLE(‘control_break’ [, ’qualifier’]) ]
-         [ UNION [ALL] | INTERSECT | EXCEPT ]
          [ * ] | [ column_spec [column_alias] [, column_spec ... ]
          [ (subquery) ]
@@ Line 39: / Line 35: @@
          [ column_spec < + | - | / | * > <column_spec | value > [column_alias] ]
          [ $uniquekey | $odxid ]
 </code>
+==== FROM datasource: ====
-==== FROM Clause Syntax: ====
+^See:^[[dev:sql:statements:select:from | FROM datasource Discussion ]]^
 <code>
   FROM table_spec [ table_alias ] [, [table_spec2 [ table_alias ] [ $omnidex ] ]
@@ Line 51: / Line 46: @@
 </code>
-==== WHERE Clause Syntax: ====
+==== WHERE criteria_specification Syntax: ====
+^See:^[[dev:sql:statements:select:where | WHERE criteria_specification Discussion ]]^
 <code>
   WHERE [ criteria_list ]
@@ Line 57: / Line 53: @@
         [ IN (subquery) ]
 </code>
-==== GROUP BY/HAVING Clause Syntax: ====
+==== GROUP BY/HAVING group_by_specification Syntax: ====
+^See: ^[[dev:sql:statements:select:group_by | GROUP BY group_by_specification Discussion]]^
 <code>
   GROUP BY group_by_columns
       [ HAVING [ left_operand operator subquery ]
-             [ left_operand IN (subquery) ] ]
+               [ left_operand IN (subquery) ] ]
 </code>
-==== ORDER BY Clause Syntax: ====
+==== ORDER BY order_by_specification Syntax:  ====
+^See:^[[dev:sql:statements:select:order_by | ORDER By order_by_specfication Discussion ]]^
 <code>
-  ORDER BY [ column_spec | column_position ] [ ASC | DESC ]
+  ORDER BY [  column_spec | column_position ] [ ASC | DESC ]
            [, column_spec | column_position ] [ ASC | DESC ]
 </code>
-==== WITH Clause Syntax: ====
+==== WITH option_list: ====
 <code>
-  WITH options
+  WITH option_list
 </code>
@@ Line 78: / Line 76: @@
 The expression_list can be one of the following:
-|  *   | All columns for the named tables using a syntax of [/[  database. ] table. ]] * |
+|  *   | All columns for the named tables using a syntax of %%[%%%%[%%database.]table.]] * |
 | column_spec | A column optionally qualified by a database and table with an optional column_alias. |
 | (subquery) | A nested subquery that returns a single select item. |
@@ Line 84: / Line 82: @@
 | $uniquekey | |
 | $odxid | |
-==== FROM Clause ====
-The From Datasource can be one of the following:
-| table_spec | A table using a syntax of [database.]table. |
-| $OMNIDEX   | An internal table automatically created and used for testing consisting of one row and one column. |
-| (subquery) | A nested subquery. |
-| JOIN Clause | Joins one or more tables.|
-==== WHERE Clause ====
-The WHERE clause can be used to specify both criteria and table join information.
-  select * from trans_table where state = 'CA';
@@ Line 100: / Line 88: @@
 ==== GROUP BY Clause ====
 ==== HAVING Clause ====
-==== ORDER BY Clause ====
-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.
-  select trans_date, trans_amount from trans_table order by trans_date;
-  select trans_date, trans_amount from trans_table order by 2;
-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;
-The ASC/DESC modifier can be used to return the data in ascending or descending order with ascending order being the default.
-  select trans_date, trans_amount from trans_table order by trans_date desc;
 ==== WITH options ====
@@ Line 129: / Line 100: @@
   > select * from mytable;
 ==== Select with Omnidex criteria on Quicktext column ====
   > select company, contact from mytable where company = 'inform*';
+==== Aggregates, Functions, Expressions =====
+  SELECT customer_no, sum(amount) FROM orders GROUP BY customer_no ORDER BY 2;
+  SELECT customer_no, count(distinct order_no) FROM orders GROUP BY customer_no ORDER BY 2, 1;
+==== Select List Item Ordinal Numbers ====
+  SELECT company, contact, state FROM customers ORDER BY 3;
+  SELECT company, contact, state FROM customers ORDER BY 3, 1;
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/statements/select/home.1278062966.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes