DISTINCT
Distinct 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 Limiters
Result 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 alias
An 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
FROM
customers, orders ...
Table-alias qualified column name(s)
SELECT
A.company, A.contact, B.order_date FROM company A, orders
B ...
Aggregate Functions
SELECT
customer_no, min(amount), max(amount), avg(amount)
FROM
orders GROUP BY customer_no
SELECT
company, sum(amount) FROM ...
SELECT
count(*), count(state) FROM ...
SQL92-Compliant Functions
These functions can be used on literals or columns.
SELECT
upper(company), upper(contact), current_date FROM ...
SELECT
position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user
FROM ...
Omnidex Extended Functions
These functions can be used on literals or columns
SELECT
$proper(company), $random(3) FROM ...
SELECT
$soundex('database') FROM ...
Literals
SELECT
'Company Name: ', company, 'Contact Name: ', contact FROM
...
Expressions
SELECT
order_number, 'Discount: ', (quantity * amount)*.02 FROM ...
$ROWID, $ODXID
$ROWID returns the internal rowid. This is either the native
rowid or the
re-defined 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 ...
top
|