SELECT Statement

Queries the contents of an input or output window. This syntax is for querying the contents of a running project. It is not valid syntax for building a project.

A SELECT statement must include at least one SELECT clause and at least one FROM clause, but may contain other optional clauses, such as WHERE, GROUP BY, and ORDER BY clauses.

Syntax

SELECT { TOP N { col1[,...] | * } } | {[DISTINCT] { expression [[AS] alias] } [, ...]}
FROM { out_window }  
[ WHERE expression ]
[ GROUP BY expression [, ...] ]
[ ORDER BY column [ ASC[ENDING] | DESC[ENDING] ] [, ...] ]

Components

N	The number of rows.
expression	A SQL-92 expression specifying selection, grouping, or filtering conditions, as appropriate. See Usage for more information.
out_window	The name of an input/output window. Identify any submodules in the path to the module containing the window, by name and separated by dots. For example: module1.out_window1.
column	The name of the column the GROUP BY clause will use to organize the output.

Usage

The SELECT statement queries the current contents of INPUT/OUTPUT windows listed in the FROM clause and generates result rows, each of which has a fixed number of columns. Rows from the INPUT/OUTPUT window listed in the FROM clause are passed to the SELECT clause, after being filtered by the query's WHERE clause, if one is specified. These results are processed by any other clauses in the query.

The SELECT statement contains the following clauses:

SELECT clause
FROM clause
(optional) WHERE clause
(optional) GROUP BY clause

SELECT Clause

Every SELECT statement must contain one SELECT clause. The SELECT clause specifies a select-list, which is then used to generate results in a query. A select-list has a number of different features, including:

The number of items in the simple SELECT statement's select-lists determines the number of columns in the result.
A select-list expression can refer to column names of the OUTPUT windows specified in the SELECT statement's FROM clause.
The asterisk (*) character can be used to select all columns from the INPUT/OUTPUT window specified in the FROM clause.
The select-list can be prefaced by a DISTINCT keyword, which makes each row included in the result unique. If two or more rows contain the same values in all queried columns, the DISTINCT keyword causes only one of the rows to be included in the result. Otherwise, all rows that fit the criteria will be included in the results.

You can also include a rowtime or rowid in the SELECT clause to display the rowtime and the rowid in the output, if required.

FROM Clause

The FROM clause specifies the data source for the SELECT statement's query. The data source must be an OUTPUT window. The OUTPUT window can be referred to by its defined name from a CREATE WINDOW statement.

WHERE Clause

The optional WHERE clause can be used to specify selection conditions. As a selection condition, the WHERE clause filters rows from the data source before they are passed to the SELECT clause. Expressions in the WHERE clause cannot have aggregate functions.

GROUP BY Clause

The optional GROUP BY clause causes one or more rows of the result to be combined into a single row of output. A GROUP BY clause is often used when the query result contains aggregate functions. The clause can contain expressions using constants or expressions from the input window or stream, but it cannot contain expressions that have aggregate functions.

Examples

This example selects all trades with the symbol 'SAP' from Trades:

SELECT TradeId, Symbol, Shares
FROM Trades
WHERE Symbol = 'SAP'

This example selects all trades with the symbol 'SAP', groups them by symbol then by price:

SELECT Symbol, Price, sum(Shares)
From Trades 
where Symbol = 'SAP'
GROUP BY Symbol, Price