select (minimum)

Syntax

Transact-SQL Syntax

select [all | distinct] select_list
 [into [[database.]owner.]table_name]
 [from [[database.]owner.]{view_name | table_name
[(index index_name [prefetch size][lru | mru])]}
 [holdlock | noholdlock] [shared]
 [,[[database.]owner.]{view_name | table_name
[(index index_name [prefetch size][lru | mru])]}
 [holdlock | noholdlock] [shared]]...]

[where search_conditions]

[group by [all]aggregate_free_expression
[, aggregate_free_expression]...]
 [having search_conditions]

[order by
 {[[[database.]owner.]{table_name. | view_name.}]
 column_name | select_list_number | expression}
 [asc | desc]
 [,{[[[database.]owner.]{table_name. | view_name.}]
 column_name | select_list_number | expression}
 [asc | desc]]...]

[compute row_aggregate(column_name)
 [, row_aggregate(column_name)]...
 [by column_name [, column_name]...]]

[for {{read only | update [of column_name_list]}]

[at isolation {read uncommitted | read committed | serializable}]

[for browse]

ODBC Syntax

SELECT [ALL|DISTINCT]select_list

FROM table_reference_list

[WHERE search_condition]

[GROUP BY column_name[,column_name]...]

[HAVING search_condition]

[UNION [ALL]select_statement]...
 [order_by_clause]

SELECT [ALL|DISTINCT]select_list

FROM table_reference_list

[WHERE search_condition]

FOR UPDATE OF [column_name[,column_name]...]

Parameters

• all – includes all rows in the result.
• from – indicates a comma-separated list of tables or views to use in the select statement.
• group by – finds a value for each group. These values appear as new columns in the results, rather than as new rows.
• order by – sorts the results by columns.
• having – sets conditions for the group by clause, similar to the way that where sets conditions for the select clause. No limit exists for the number of conditions that can be included.
• union – returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.
• read only – indicates that the cursor is a read-only cursor and that updates cannot be applied to rows made current by it.
• update – indicates that the cursor is an updatable cursor, and that the rows it makes current can be deleted or updated.
• select_list – is one or more of these items:

  • A list of column names in the order in which you want them returned

  • An aggregate function

  • Any combination of these items

• table_name – and view_name list tables and views used in the select statement.
  If more than one table or view is in the list, the names are separated by commas. Table names and view names are given correlating names. You can do this by providing the table or view name, then a space, then the correlation name, for example:

  select * from publishers t1, authors t2

• search_conditions – sets the conditions for the rows that are retrieved. A search condition can include column names, constants, joins, the keywords is null, is not null, or, like, and, or any combination of these items.

Examples

• Example 1 – counts publishers with read-only status.

  select count(*) from publishers for read only

• Example 2 – selects pub_id, pub_name, city, and name from publishers.

  select pub_id, pub_name, city, state from publishers for read only

• Example 3 – selects type and price from titles table.

  select type, price from titles 
   where price > @p1 for update of price

• Example 4 – selects stor_id and stor_name from sales_east table.

  select stor_id, stor_name from sales union
   select stor_id, stor_name from sales_east

Usage