|
SQL Reference |
SELECT Statement |
SELECT List
|
SELECT ListResult set limiters (TOP, EVERY, RANDOM) Table-qualified column name(s) Table-alias qualified column name(s)
DISTINCTDistinct can be applied to an entire select-item list or used within an aggregate function. SELECT DISTINCT city, state FROM prospects SELECT count(DISTINCT zip_code) FROM prospects
Result Set LimitersResult set limiters limit the rows returned by the select statement. The result set limiter processing is done AFTER the query has completed. TOP n, EVERY n, RANDOM n SELECT TOP 10 company FROM prospects SELECT EVERY 20 company FROM prospects SELECT RANDOM 10 company FROM prospects These result set limiters do not order returned data in any way. Ordering is set by the ORDER BY clause.
Column name(s)Column names from one or more tables. SELECT company, contact, phone FROM customers SELECT company, contact, order_date FROM customers, orders ...
Column name(s) with an aliasAn alias containing a space or special character must be quoted. Single or double quotes. The AS keyword is optional. SELECT company CO, contact 'Contact Name', phone "Phone Number" FROM ... SELECT contact AS 'Contact Name', phone AS 'Phone #' FROM ...
Table-qualified column name(s)SELECT customers.company, customers.contact, orders.order_date
Table-alias qualified column name(s)SELECT A.company, A.contact, B.order_date FROM company A, orders B ...
Aggregate Functionsmin() SELECT company, sum(amount) FROM ... SELECT count(*), count(state) FROM ...
SQL92-Compliant FunctionsThese functions can be used on literals or columns. CASE SELECT upper(company), upper(contact), current_date FROM ... SELECT position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user FROM ...
Omnidex Extended FunctionsThese functions can be used on literals or columns $COLUMN_LENGTH | $COL_LEN SELECT $proper(company), $random(3) FROM ... SELECT $soundex('database') FROM ...
LiteralsSELECT 'Company Name: ', company, 'Contact Name: ', contact FROM ...
ExpressionsSELECT order_number, 'Discount: ', (quantity * amount)*.02 FROM ...
$ROWID, $ODXID$ROWID returns the internal rowid. This is either the native rowid or the redefined rowid. $ODXID is the Omnidex ID from the Omnidex indexes. SELECT $ROWID, $ODXID, Company FROM ...
* (Asterisk)SELECT * FROM CUSTOMERS ... SELECT c.*, o.* FROM CUSTOMERS c, ORDERS o ...
Nested Select StatementSELECT STATE, DESCRIPTION,
|
|
APIs | SQL Reference | Stored Procedures | Third Party Tools | Debugging |
OmniAccess API |
ODBC |
JDBC |
Nested Queries |
SET Operations |
WITH Options |
CURSOR / INSTANCE |
Commands |
Functions |
SQL Server |
Oracle |
MS Access |
Ms Excel |