Specify DISTINCT to eliminate duplicate rows from the results. If you do not specify DISTINCT, all rows that satisfy the
clauses of the SELECT statement are returned, including duplicate rows. Many statements take significantly longer to execute
when DISTINCT is specified, so you should reserve DISTINCT for cases where it is necessary.
Use row limitations to return a subset of the results. For example, specify FIRST to retrieve the first row of a result
set. Use TOPn to return the first n rows of the results. Specify START ATm to control the location of the starting row when retrieving the TOPn rows. To order the rows so that these clauses return meaningful results, specify an ORDER BY clause for the SELECT statement.
A list of expressions specifying what to retrieve from the database. Usually, the expressions in a select list are column
names. However, they can be other types of expressions, such as functions. Use an asterisk (*) to select all columns of all
tables listed in the FROM clause. Optionally, you can define an alias for each expression in the select-list. Using an alias allows you to reference the select-list expressions from elsewhere in the query, such as from within the WHERE and ORDER BY clauses.
Rows are retrieved from the tables and views specified in the table-expression. See FROM clause [UltraLite].
GROUP BY clause
The result of the query that has a GROUP BY clause contains one row for each distinct set of values in the GROUP BY expression.
The resulting rows are often referred to as groups since there is one row in the result for each group of rows from the table
list. Aggregate functions can be applied to the rows in these groups. NULL is considered to be a unique value if it occurs.
ORDER BY clause
This clause sorts the results of a query according to the expression specified in the clause. Each expression in the ORDER
BY clause can be sorted in ascending (ASC) or descending (DESC) order (the default). If the expression is an integer n, then the query results are sorted by the nth expression in the select list.
The only way to ensure that rows are returned in a particular order is to use ORDER BY. In the absence of an ORDER BY clause,
UltraLite returns rows in whatever order is most efficient.
This clause has two variations that control the query's behavior:
FOR READ ONLY
This clause indicates the query is not being used for updates. You should specify this clause whenever possible, since
UltraLite can sometimes achieve better performance when it is known that a query is not going to be used for updates. For
example, UltraLite could perform a direct table scan when it learns that read-only access is required. FOR READ ONLY is the
default behavior. See Using direct page scans.
This clause allows the query to be used for updates. This clause must be explicitly specified otherwise updates are
not permitted (FOR READ ONLY is the default behavior).
OPTION ( FORCE ORDER ) clause
This clause is not recommended for general use. It overrides the UltraLite choice of the order in which to access tables,
and requires that UltraLite access the tables in the order they appear in the query. Only use this clause when the query order
is determined to be more efficient than the UltraLite order.