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

SQL Anywhere 11.0.0 » UltraLite - Database Management and Reference » UltraLite SQL Reference » UltraLite SQL statement reference


UltraLite SELECT statement

Use this statement to retrieve information from the database.

SELECT [ DISTINCT ] [ row-limitation  ] 
[ FROM table-expression, ... ]
[ WHERE search-condition ]
[ GROUP BY group-by-expression, ... ]
[ HAVING search-condition ]
[ ORDER BY order-by-expression, ... ]
row-limitation : 
| TOP n [ START AT m ]
select-list :
expression [ [ AS ] alias-name ], ...
order-by-expression :
{ integer | expression } [ ASC | DESC ]

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

row-limitation clause   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.

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

FROM clause   Rows are retrieved from the tables and views specified in the table-expression. See UltraLite 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 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.

HAVING clause   This clause selects rows based on the group values and not on the individual row values. Therefore, you can only use the HAVING clause if there is also a GROUP BY clause, or if the select-list consists solely of aggregate functions. The search condition can only involve aggregate expressions and expressions occurring in the GROUP BY clause. The HAVING condition is tested after rows have been grouped.

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.

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

  • FOR UPDATE   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.

UltraLite can also use execution plans to optimize queries. See Execution 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 Employees table.

FROM Employees;

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

SELECT TOP 10 START AT 40 * FROM Employees;