Use this statement to retrieve information from the database.

Syntax

SELECT [ DISTINCT ] [ row-limitation  ] 
select-list
[ FROM table-expression, ... ]
[ WHERE search-condition ]
[ GROUP BY group-by-expression, ... ]
[ HAVING search-condition ]
[ ORDER BY order-by-expression, ... ]
[ FOR  { UPDATE | READ ONLY } ]
[ OPTION ( FORCE ORDER ) ]

row-limitation : 
FIRST 
| TOP n [ START AT m ]

select-list :
expression [ [ AS ] alias-name ], ...

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

Parameters

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:

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.

Remarks

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

See also

• Optimizing UltraLite query performance
• SELECT statement
• Querying data

Example

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

SELECT COUNT(*)
FROM Employees;

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

SELECT TOP 10 START AT 40 * FROM Employees;