Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Statement Reference

UltraLite ROLLBACK statement Next Page

UltraLite SELECT statement

Retrieves information from the database.


[ FROM table-expression, ... table-expression ]
[ WHERE search-condition ]
[ GROUP BY group-by-expression, ]
[ HAVING search-condition ]
[ ORDER BY order-by-expression,...order-by-expression ]

select-list :
{ column-name | column-expression } [ AS ]

order-by-expression :
{ integer | expression } [ ASC | DESC ]


select-list    A list of expressions, separated by commas, specifying what will be retrieved from the database. Use an asterisk (*) to select all columns of all tables in the FROM clause.

You can define an alias name following the expression in the select-list to represent that expression. The alias name can then be used elsewhere in the query, such as in the WHERE clause or ORDER BY clause.

DISTINCT clause    If you do not specify DISTINCT, all rows that satisfy the clauses of the SELECT statement are returned. If DISTINCT is specified, duplicate output rows are eliminated. Many statements take significantly longer to execute when DISTINCT is specified, so you should reserve DISTINCT for cases where it is necessary.

FIRST or TOP or START AT clause    You can explicitly retrieve only the first row of a result set or the first n rows of a result set. The TOP and START AT clauses provide additional flexibility in queries by explicitly limiting the result set by parameterizing it with specific instructions: the TOP clause specifies the number of rows to return and the START AT clause sets the starting point result set.

FROM clause    Rows are retrieved from the tables and views specified in the table-expression. See FROM clause.

WHERE clause    If a WHERE clause is specified, only rows satisfying search-condition are selected. See Search conditions in UltraLite.

GROUP BY clause    The result of the query contains one row for each distinct set of values in the GROUP BY expressions. 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.

HAVING clause    This clause selects rows based on the group values and not on the individual row values. The HAVING expressions can only be used if either the statement has a GROUP BY clause or the select list consists solely of aggregate functions. The search condition must be an aggregate expression. It can only involve aggregate expressions and expressions occurring in the GROUP BY clause. The HAVING condition is tested after a candidate row has been completely grouped.

ORDER BY clause    This clause sorts the results of a query according to the expression specified in the clause. Each item in the ORDER BY list can be labeled as ASC for ascending order (the default) or DESC for descending order. If the expression is an integer n, then the query results are sorted by the nth item 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.

FOR clause    This clause has two variations that control the query's 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.

UltraLite can also use query access plans to optimize queries. See Query access plans in UltraLite.


Always remember to close the query. Otherwise memory cannot be freed and the number of temporary tables that remain can proliferate unnecessarily.

See also

The following statement selects the number of employees from the employee table.

FROM employee

The following statement selects 10 rows from the employee table starting from the 40th row and ending at the 49th row.

SELECT TOP 10 START AT 40 * FROM employee

FROM clause