Promotic

SELECT - statement of language DB_MSSQL

Description:
Data query. The result is always in the table form.
Syntax:

SELECT [ TOP lines | TOP percent PERCENT ] select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

lines Limit the number of received rows.
percent Limit the number of received rows expressed in percentage (0-100).
select_list List of columns with the query results (expressions separated by comma) or the '*' character (means all columns).
new_table Name of new table that was created as a result of the query.
table_source List of tables used in the query (tables separated by comma).
search_condition Logical expression stating which rows of source tables to include into the query result (if WHERE is not stated then it means all records).
order_expression Specifies the column according to which the query result is sorted.
ASC Sorting is ascending (default)
DESC Sorting is descending
Note:
The syntax of the SELECT statement is de facto much more complex and it contains for example clauses GROUP BY and HAVING that enable to perform group operations (summaries, averages, counts) above groups of records.
See also:
Example1:
Obtains all columns for all records from the table data.
SELECT * FROM data
Example2:
Obtains all columns for the first 10 records from the table data (sorted by the time).
SELECT TOP 10 * FROM data ORDER BY time
Example3:
Obtains all columns for records that meet the specified condition (the time is greater than 2-Jul-2003 10:02:00) from the table data, the result is sorted ascending according to the column time. Both notations are equivalent (the time with the precision to ms can be stated in both formats).
SELECT * FROM data WHERE time>'2003-07-02 10:02:00' ORDER BY time
SELECT * FROM data WHERE time>'07/02/2003 10:02:00.000' ORDER BY time
Example4:
Obtains selected columns that meet the specified condition for records (the value 'value' is in the range from 20 to 80) from the table data, the result is sorted ascending according to the column time. Both notations are equivalent.
SELECT time, value FROM data WHERE value > 20 AND value < 80 ORDER BY time
SELECT time, value FROM data WHERE value BETWEEN 20 AND 80 ORDER BY time
Example5:
Obtains the number of records, the sum of values and the average value for the records that meet the specified condition from the table data where calculated columns are named by the specified names.
SELECT COUNT(*) AS Count, SUM(value) AS Suma, AVG(value) AS Average FROM data WHERE value BETWEEN 20 AND 80
Example6:
Obtains the column time and the calculated column (the value 'value' is rounded to the integer number) of all records from the table data.
SELECT time, ROUND(value,0) AS Round_0 FROM data
Example7:
Obtains all columns for none record (WHERE 1=0 is always invalid) from the table data and stores the result into a new table data_copy. It means that only the table structure is copied. The clause WHERE specifies whether and which records to copy into the new table.
SELECT * INTO data_copy FROM data WHERE 1=0
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r.o.