SELECT Examples (Transact-SQL)

Applies to: SQL Server (all supported versions) Azure SQL Database

This topic provides examples of using the SELECT statement.

A. Using SELECT to retrieve rows and columns

The following example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the * ) from the Product table in the AdventureWorks2012 database.

This example returns all rows (no WHERE clause is specified), and only a subset of the columns ( Name , ProductNumber , ListPrice ) from the Product table in the AdventureWorks2012 database. Additionally, a column heading is added.

This example returns only the rows for Product that have a product line of R and that have days to manufacture that is less than 4 .

B. Using SELECT with column headings and calculations

The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product.

This is the query that calculates the revenue for each product in each sales order.

C. Using DISTINCT with SELECT

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

D. Creating tables with SELECT INTO

The following first example creates a temporary table named #Bicycles in tempdb .

This second example creates the permanent table NewProducts .

E. Using correlated subqueries

A correlated subquery is a query that depends on the outer query for its values. This query can be executed repeatedly, one time for each row that may be selected by the outer query.

The first example shows queries that are semantically equivalent to illustrate the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery that retrieves one instance of each product name for which the product model is a long sleeve logo jersey, and the ProductModelID numbers match between the Product and ProductModel tables.

The next example uses IN and retrieves one instance of the first and last name of each employee for which the bonus in the SalesPerson table is 5000.00 and for which the employee identification numbers match in the Employee and SalesPerson tables.

The previous subquery in this statement cannot be evaluated independently of the outer query. It requires a value for Employee.EmployeeID , but this value changes as the SQL Server Database Engine examines different rows in Employee .

A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the product models for which the maximum list price is more than twice the average for the model.

This example uses two correlated subqueries to find the names of employees who have sold a particular product.

F. Using GROUP BY

The following example finds the total of each sales order in the database.

Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.

G. Using GROUP BY with multiple groups

The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.

H. Using GROUP BY and WHERE

The following example puts the results into groups after retrieving only the rows with list prices greater than $1000 .

I. Using GROUP BY with an expression

The following example groups by an expression. You can group by an expression if the expression does not include aggregate functions.

J. Using GROUP BY with ORDER BY

The following example finds the average price of each type of product and orders the results by average price.

K. Using the HAVING clause

The first example that follows shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less. The second example shows a HAVING clause without aggregate functions.

This query uses the LIKE clause in the HAVING clause.

L. Using HAVING and GROUP BY

The following example shows using GROUP BY , HAVING , WHERE , and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. It also organizes the results by ProductID .

M. Using HAVING with SUM and AVG

The following example groups the SalesOrderDetail table by product ID and includes only those groups of products that have orders totaling more than $1000000.00 and whose average order quantities are less than 3 .

To see the products that have had total sales greater than $2000000.00 , use this query:

If you want to make sure there are at least one thousand five hundred items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold. The query looks like this:

N. Using the INDEX optimizer hint

The following example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table, and the second example forces a table scan by using an index of 0.

M. Using OPTION and the GROUP hints

The following example shows how the OPTION (GROUP) clause is used with a GROUP BY clause.

O. Using the UNION query hint

The following example uses the MERGE UNION query hint.

P. Using a simple UNION

In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.

Q. Using SELECT INTO with UNION

In the following example, the INTO clause in the second SELECT statement specifies that the table named ProductResults holds the final result set of the union of the designated columns of the ProductModel and Gloves tables. Note that the Gloves table is created in the first SELECT statement.

R. Using UNION of two SELECT statements with ORDER BY

The order of certain parameters used with the UNION clause is important. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.

S. Using UNION of three SELECT statements to show the effects of ALL and parentheses

The following examples use UNION to combine the results of three tables that all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.

The third example uses ALL with the first UNION and parentheses enclose the second UNION that is not using ALL . The second UNION is processed first because it is in parentheses, and returns 5 rows because the ALL option is not used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT by using the UNION ALL keywords. This does not remove the duplicates between the two sets of 5 rows. The final result has 10 rows.

Select function in Power Apps

Simulates a select action on a control, causing the OnSelect formula to be evaluated.

Description

The Select function simulates a select action on a control as if the user had clicked or tapped the control. As a result, the OnSelect formula on the target control is evaluated.

Use Select to propagate a select action to a parent control. This type of propagation is the default behavior in, for example, galleries. By default, the OnSelect property of any control in a Gallery control is set to Select( Parent ). That way, you can set the value of the OnSelect property of the gallery control itself, and that formula will be evaluated regardless of where in the gallery a user might click or tap.

If you want one or more controls in the gallery to perform different actions from the gallery itself, set the OnSelect property for those controls to something other than the default value. You can leave the default values for the OnSelect properties of most controls in the gallery if you want them to perform the same action as the gallery itself.

Select queues the target OnSelect for later processing, which may happen after the current formula has finished being evaluated. Select doesn’t cause the target OnSelect to evaluate immediately, nor does Select wait for OnSelect to finish being evaluated.

You can’t use Select across screens.

You can use Select only with controls that have an OnSelect property.

You can use Select only in behavior formulas.

A control can’t Select itself directly or indirectly through other controls.

The select function can also be used with a gallery. For example, it can be used to specify the row or column to select in a gallery and the control to select within that row or column of the gallery. When you select a row or column, the gallery selection changes and the OnSelect formula on the gallery control is evaluated. If a control within the row or column is provided, the OnSelect formula for the child control will be evaluated.

Syntax

Select( Control )

• Control – Required. The control to select on behalf of the user.

Select( Control, Row or column, Child Control )

• Control – Required. The control to select on behalf of the user.
• Row or column – Not required. The number of row or column (starting with 1) in a gallery control to select on behalf of the user.
• Child Control — Not required. The child control of the control identified in the ‘control’ parameter to select.

Examples

Simulates a user selecting row 1 or column 1 in Gallery1.

Select(Gallery1, 1, ChildControl1)

Simulates a user selecting ChildConttrol1 in row 1 or column 1 of Gallery1.

Basic usage

Add a Button control, and rename it Button1 if it has a different name.

Set the OnSelect property of Button1 to this formula:

Notify( «Hello World» )

On the same screen, add a second Button control, and set its OnSelect property to this formula:

Select( Button1 )

While holding down the Alt key, select the second button.

A notification appears across the top of your app. The OnSelect property of Button1 generated this notification.

Gallery control

Add a vertical Gallery control that contains other controls.

Set the OnSelect property of the gallery to this formula:

Notify( «Gallery Selected» )

While holding down the Alt key, click or tap the background of the gallery or any control in the gallery.

All actions will show the Gallery Selected notification at the top of the app.

Use the gallery’s OnSelect property to specify the default action to take when the user clicks or taps an item in the gallery.

Set the OnSelect property of the image control to this formula:

Notify( «Image Selected», Success )

While holding down the Alt key, click or tap the various elements of the gallery.

When you click or tap any control in the gallery except the image, Gallery Selected appears as before. When you click or tap the image, Image Selected appears.

Use individual controls in the gallery to take actions that differ from the gallery’s default action.

On the same screen, add a Button control, and set its OnSelect property to this formula:

Select( Gallery1,2,Image1 )

While holding down the Alt key, select the button.

A Image Selected notification appears across the top of your app. The button click simulated selecting the image in row 2 of the gallery.

Can you tell us about your documentation language preferences? Take a short survey.

The survey will take about seven minutes. No personal data is collected (privacy statement).

SELECT — OVER Clause (Transact-SQL)

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

Transact-SQL Syntax Conventions

Syntax

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

Window functions might have the following arguments in their OVER clause:

• PARTITION BY that divides the query result set into partitions.
• ORDER BY that defines the logical order of the rows within each partition of the result set.
• ROWS/RANGE that limits the rows within the partition by specifying start and end points within the partition. It requires ORDER BY argument and the default value is from the start of partition to the current element if the ORDER BY argument is specified.

If you don’t specify any argument, the window functions will be applied on the entire result set.

object_id	min	max
3	3	2139154666
5	3	2139154666
.	.	.
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

If PARTITION BY is not specified, the function treats all rows of the query result set as a single partition. Function will be applied on all rows in the partition if you don’t specify ORDER BY clause.

PARTITION BY value_expression

Specifies the column by which the rowset is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

object_id	type	min	max
68195293	PK	68195293	711673583
631673298	PK	68195293	711673583
711673583	PK	68195293	711673583
.	.	.
3	S	3	98
5	S	3	98
.	.	.
98	S	3	98
.	.	.

ORDER BY

Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

• If it is not specified, the default order is ASC and window function will use all rows in partition.
• If it is specified, and a ROWS/RANGE is not specified, then default RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame by the functions that can accept optional ROWS/RANGE specification (for example min or max ).

object_id	type	min	max
68195293	PK	68195293	68195293
631673298	PK	68195293	631673298
711673583	PK	68195293	711673583
.	.	.
3	S	3	3
5	S	3	5
6	S	3	6
.	.	.
97	S	3	97
98	S	3	98
.	.	.

order_by_expression
Specifies a column or expression on which to sort. order_by_expression can only refer to columns made available by the FROM clause. An integer cannot be specified to represent a column name or alias.

COLLATE collation_name
Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name can be either a Windows collation name or a SQL collation name. For more information, see Collation and Unicode Support. COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC is the default sort order. Null values are treated as the lowest possible values.

ROWS or RANGE

Applies to: SQL Server 2012 (11.x) and later.

Further limits the rows within the partition by specifying start and end points within the partition. This is done by specifying a range of rows with respect to the current row either by logical association or physical association. Physical association is achieved by using the ROWS clause.

The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. Preceding and following rows are defined based on the ordering in the ORDER BY clause. The window frame «RANGE . CURRENT ROW . » includes all rows that have the same values in the ORDER BY expression as the current row. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.

object_id	preceding	central	following
3	1	3	156
5	2	4	155
6	3	5	154
7	4	5	153
8	5	5	152
.	.	.	.
2112726579	153	5	4
2119678599	154	5	3
2123154609	155	4	2
2139154666	156	3	1

ROWS or RANGE requires that the ORDER BY clause be specified. If ORDER BY contains multiple order expressions, CURRENT ROW FOR RANGE considers all columns in the ORDER BY list when determining the current row.

UNBOUNDED PRECEDING

Applies to: SQL Server 2012 (11.x) and later.

Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as window starting point.

PRECEDING
Specified with to indicate the number of rows or values to precede the current row. This specification is not allowed for RANGE.

CURRENT ROW

Applies to: SQL Server 2012 (11.x) and later.

Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW can be specified as both a starting and ending point.

BETWEEN AND

Applies to: SQL Server 2012 (11.x) and later.

Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. defines the boundary starting point and defines the boundary end point. The upper bound cannot be smaller than the lower bound.

UNBOUNDED FOLLOWING

Applies to: SQL Server 2012 (11.x) and later.

Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window end point. For example RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current row and ends with the last row of the partition.

FOLLOWING
Specified with to indicate the number of rows or values to follow the current row. When FOLLOWING is specified as the window starting point, the ending point must be FOLLOWING. For example, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row that follows the current row and ends with the tenth row that follows the current row. This specification is not allowed for RANGE.

unsigned integer literal
Applies to: SQL Server 2012 (11.x) and later.

Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. This specification is valid only for ROWS.

General Remarks

More than one window function can be used in a single query with a single FROM clause. The OVER clause for each function can differ in partitioning and ordering.

If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Important!

If ROWS/RANGE is specified and is used for (short syntax) then this specification is used for the window frame boundary starting point and CURRENT ROW is used for the boundary ending point. For example «ROWS 5 PRECEDING» is equal to «ROWS BETWEEN 5 PRECEDING AND CURRENT ROW».

If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.

Limitations and Restrictions

The OVER clause cannot be used with the CHECKSUM aggregate function.

RANGE cannot be used with PRECEDING or FOLLOWING.

Depending on the ranking, aggregate, or analytic function used with the OVER clause, and/or the may not be supported.

Examples

A. Using the OVER clause with the ROW_NUMBER function

The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD . The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.

Here is the result set.

B. Using the OVER clause with aggregate functions

The following example uses the OVER clause with aggregate functions over all rows returned by the query. In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

Here is the result set.

The following example shows using the OVER clause with an aggregate function in a calculated value.

Here is the result set. Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each SalesOrderID .

C. Producing a moving average and cumulative total

The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson table. The data is partitioned by TerritoryID and logically ordered by SalesYTD . This means that the AVG function is computed for each territory based on the sales year. Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

Here is the result set.

In this example, the OVER clause does not include PARTITION BY. This means that the function will be applied to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

Here is the result set.

D. Specifying the ROWS clause

Applies to: SQL Server 2012 (11.x) and later.

The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).

Here is the result set.

In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. The result is that the window starts at the first row of the partition.

Here is the result set.

Examples: Parallel Data Warehouse

E. Using the OVER clause with the ROW_NUMBER function

The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

Here is a partial result set.

F. Using the OVER clause with aggregate functions

The following examples show using the OVER clause with aggregate functions. In this example, using the OVER clause is more efficient than using subqueries.

Here is the result set.

The following example shows using the OVER clause with an aggregate function in a calculated value. Notice that the aggregates are calculated by SalesOrderNumber and the percentage of the total sales order is calculated for each line of each SalesOrderNumber .