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 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 names from one or more tables.

SELECT company, contact, phone FROM customers

SELECT company, contact, order_date FROM customers, orders ...

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 ...

SELECT customers.company, customers.contact, orders.order_date
  FROM customers, orders ...

SELECT A.company, A.contact, B.order_date FROM company A, orders B ...

min() max() avg() sum() count() count(*)

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 ...

These functions can be used on literals or columns.

CASE
CAST
CHARACTER_LENGTH | CHAR_LENGTH
Concatenation using ||
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
EXTRACT
LOWER
POSITION
SESSION_USER
SUBSTRING
SYSTEM_USER
TRIM
UPPER
USER

SELECT upper(company), upper(contact), current_date FROM ...

SELECT position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user FROM ...

Extended Functions

$CURRENT_ROW Return the current row number.

$EXTERNAL Execute an external user-defined function.

$IFNULL Specify a return value for columns containing null values.

$LJ Left justify a string by eliminating leading white space.

$LOOKUP Retrieve textual metadata.

$LPAD Add leading “PAD” characters to a string.

$MOD Return “n modulus y” (remainder).

$PROPER Shift the first letter of each word in a string to upper case and all other letters to lower case.

$RANDOM Return a pseudo-random number.

$RJ Right justify a string by eliminating trailing white space and inserting leading spaces as needed.

$ROUND Round a numerical value to the specified number of decimal places.

$RPAD Add trailing “PAD” characters to a string.

$SCORE Returns the rank/relevancy score of qualified text from a $CONTAINS function.

$SOUNDEX Return the Soundex equivalent to a character string.

$TRUNC Return a numeric expression truncated to a specified number of digits to the right of the decimal point.

These functions can be used on literals or columns

$COLUMN_LENGTH | $COL_LEN
$CONVERT
$CURRENT_ROW
$EXTERNAL
$IFNULL
$LJ
$LPAD
$MOD
$PROPER
$RANDOM
$RJ
$ROUND
$RPAD
$SOUNDEX
$TRUNC

SELECT $proper(company), $random(3) FROM ...

SELECT $soundex('database') FROM ...

SELECT 'Company Name: ', company, 'Contact Name: ', contact FROM ...

SELECT order_number, 'Discount: ', (quantity * amount)*.02 FROM ...

$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 ...

SELECT * FROM CUSTOMERS ...

SELECT c.*, o.* FROM CUSTOMERS c, ORDERS o ...

SELECT STATE, DESCRIPTION,
  (SELECT SUM(TOTAL) FROM ORDERS WHERE TAX_STATE='CO')
  FROM STATES WHERE STATE='CO'

© Copyright Dynamic Information Systems - This document was last updated July 15, 2009.