select (minimum)

Description

Retrieves rows from database objects.

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]

An alternate syntax for updating tables if the driver supports core or extended functionality:

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 the following 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, such as:

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

select count(*) from publishers for read only

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

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

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

Usage

• You can issue this command as a language command or a client-based cursor request.

• This statement is accepted and sent to ODBC without change, subject to the qualifications listed in this section.

• The TEXTPTR() function cannot appear in the select list.

• The following SQL Server 10.x aggregate functions are supported:

  • sum ( [all | distinct] )

  • avg ( [all | distinct] )

  • count ( [all | distinct] )

  • count (*)

  • max (expression))

  • min (expression))

• If the command is issued as a cursor, the access service library supports the following cursor commands:

  • declare

  • open

  • fetch

  • close

  • deallocate

• If a cursor is passed a new set of parameters before it is opened, it can be reused multiple times.

• The data values used in the where clause search conditions are passed as cursor parameters, using the datatype associated with the column.

• Cursor parameters are indicated with the “@” character when Transact-SQL syntax is used, and with a question mark when passthrough mode is used.

• The following are not supported:

  • Transact-SQL select into syntax

  • The use of index in a from clause

  • The use of prefetch size in a from clause

  • The use of holdlock|noholdlock|shared in a from clause

  • The “compute” phrase

  • The “at isolation” phrase

  • The “for browse” phrase

• The availability of the GROUP BY, HAVING, and UNION clauses depends upon the ODBC driver level of conformance.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF