Share via


SELECT Clause (SQL Server Compact)

Specifies the columns to be returned by the query.

Syntax

SELECT [ ALL | DISTINCT ] TOP (expression) < select_list > 
< select_list > ::= 
   { * 
   | { table_name | table_alias }.* 
   | { column_name | expression } [ [ AS ] column_alias ] 
   } [ ,...n ] 

Arguments

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

  • DISTINCT
    Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

  • TOP (expression)
    Indicates that only a specified first set or percent of rows will be returned from the query result set. expression can be either a number or a percent of the rows.

    The TOP clause can be used in SELECT, INSERT, UPDATE, and DELETE statements. Parentheses delimiting expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, using the TOP expression without parentheses in SELECT statements is supported, but we do not recommend it. For more information, see TOP (SQL Server Compact).

  • < select_list >
    The columns to be selected for the result set. The select list is a series of expressions separated by commas.

  • *
    Specifies that all columns from all tables in the FROM clause should be returned. The columns are returned by table, as specified in the FROM clause, and in the order in which they occur in the table.

  • table_name| table_alias.*
    Limits the scope of the * to the specified table. Requires that all columns from the specified table in the FROM clause should be returned. The columns are returned in the order in which they occur in the table. If a table has an alias specified in the FROM clause, the alias must be used. Using the table name is not valid.

  • column_name
    The name of a column to return. Qualify column_name to prevent an ambiguous reference, such as when two tables in the FROM clause have columns with duplicate names. For example, if both the 'A' and 'B' tables in a database have a column named 'itemID', and the two tables are joined in a query, the itemID must be specified in the SELECT list as 'A.itemID' and 'B.itemID'. If a table alias is provided, the table alias must be used to qualify the column name. Otherwise, use the full table name.

  • expression
    A column name, constant, function, or any valid combination of column names, constants, and functions connected by one or more operators.

  • column_alias
    An alternative name to replace the column name in the query results set. For example, an alias such as "Quantity", "Quantity to Date", or "Qty" can be specified for a column named "quantity". Aliases are used also to specify names for the results of expressions, for example:

    SELECT AVG([Unit Price]) AS [Average Price] FROM [Order Details]
    

    Note

    column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.