SELECT

The SELECT clause specifies the fields of the output records to be returned by the query.

Arguments:

TOP n

Specifies that only the first n records are to be output from the query result set. If the query includes an ORDER BY clause, the first n records ordered by the ORDER BY clause are output. If the query has no ORDER BY clause, the order of the records is arbitrary.
For more information, see Retrieving a Fixed Number of Records.

ALL

Specifies that duplicate records can appear in the result set. ALL is the default.

DISTINCT

Specifies that only unique records can appear in the result set. NULL values are considered equal for the purposes of the DISTINCT keyword.
For more information, see Eliminating Duplicate Values.

<selection_list>

The fields to be selected for the result set. The selection list is a series of field-expressions separated by commas.

*

Specifies that all the input record fields should be returned. The fields are returned in the order in which they are exported by the Input Format.

AS <alias>

Specifies an alternative name to replace the field name in the query result set. By default, output formats that display field names use the text of a field-expression in the SELECT clause as the name of the corresponding output record field. However, when a field-expression in the SELECT clause has been aliased, output formats will use the alias as the name of the output record field.
The alias of a field-expression can be also used anywhere else in the query as a shortcut that refers to the original field-expression.

Remarks:

• When a field-expression is aliased with an alias matching an input record field name, the aliasing will affect that field-expression only; any other occurrence of the alias in the query will resolve to the input record field name.
  As an example, the output records of the following query are made up of two fields with an identical name ("TimeGenerated"); the first output record field will contain values from the aliased field-expression ("ADD(EventID, 1000)"), while the second output record field will contain values from the "TimeGenerated" input format field:

  SELECT ADD(EventID, 1000) AS TimeGenerated, TimeGenerated
  FROM system
  

• A field-expression in the SELECT clause can refer to aliases defined elsewhere in the SELECT clause, as long as the definition happens before (in a left-to-right order) its use.
  The following example is a correct SELECT clause:

  SELECT EventID AS MyAlias, ADD(MyAlias, 100)
  

  On the other hand, the following example is not a correct SELECT clause, since the "MyAlias" alias is used before being defined:

  SELECT ADD(MyAlias, 100), EventID AS MyAlias
  

Examples:

A. Selecting specific fields

The following query selects a subset of all the fields exported by the EVT Input Format:

SELECT TimeGenerated, SourceName
FROM System

B. Selecting specific fields and field-expressions

The following query selects a constant and a function that uses a field exported by the EVT Input Format as argument:

SELECT 'Event Type:', EXTRACT_TOKEN(EventTypeName, 0, ' ')
FROM System

C. Selecting all fields with *

The following query selects all the fields exported by the EVT Input Format:

SELECT *
FROM System

D. Using TOP

The following query returns the 10 most requested Url's in the specified IISW3C log file:

SELECT TOP 10 cs-uri-stem, COUNT(*)
FROM ex040305.log
GROUP BY cs-uri-stem
ORDER BY COUNT(*) DESC

E. Using DISTINCT

The following query uses the REG Input Format to return all the registry key value types that are found under the specified key:

SELECT DISTINCT ValueType
FROM \HKLM\SYSTEM\CurrentControlSet

F. Aliasing field-expressions

The following query returns a breakdown of page requests per page type from the specified IISW3C log file:

SELECT TO_UPPERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType, COUNT(*) AS TotalHits
FROM ex040305.log
GROUP BY PageType
ORDER BY TotalHits DESC

See also:

Basics of a Query
Eliminating Duplicate Values
Retrieving a Fixed Number of Records

© 2004 Microsoft Corporation. All rights reserved.