SELECT Statement

Use the SELECT statement to retrieve rows. The SELECT statement specifies the columns of interest, the scope (the set of files) for the search, and the search criteria. If the results must be ordered, you can add an additional ORDER BY clause to return the rows in ascending or descending order.

Syntax

SELECT  Select_List  | *
        FROM_Clause 
        [WHERE_Clause ]
        [ORDER_BY_Clause ]

Elements

Select_List	Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query. A column alias can be either a well-known Index Server friendly name or a new friendly name (regular_identifier) as defined by a SET PROPERTYNAME statement. Note Do not use "true" SQL expressions, such as: "SIZE + 500", and LENGTH(DocAuthor), in the Select_List. These expressions are not supported.

* (asterisk)	Specifies all columns. Note Use an asterisk with SELECT only when the FROM_Clause references a predefined VIEW or a temporary VIEW.

FROM_Clause	Specifies the files on which to perform the search.

WHERE_Clause	Specifies which rows in the virtual table defined by the FROM_Clause make up the resulting rowset. This clause is optional.

ORDER_BY_Clause	Specifies the ordering of the resulting rowset. This clause is optional.

Remarks

SQL-92 specifies both the ALL and DISTINCT set quantifiers. Because Index Server retrieves all rows (files) including duplicates, the ALL set quantifier is assumed and does not have to be specified. DISTINCT is not supported.

Examples

The following query returns the author, size, title, and the last time the file was modified, for all files on the virtual root ( / ) that contain the phrase "Index Server".
```
SELECT DocAuthor, size,  DocTitle,  write
FROM  SCOPE()
WHERE CONTAINS(Contents, '"Index Server"') > 0
```
For the following query to work, #MyDocView must have first been defined with a CREATE VIEW statement. Suppose #MyDocView was defined as a VIEW containing the properties DocAuthor, FileName, size, and access. Using an asterisk selects all the properties in that VIEW. The statement returns DocAuthor, FileName, size, and access values for all rows where the file size is greater than 100000.
```
SELECT * FROM #MyDocView WHERE size > 100000
```