Using UNION of two SELECT statements with ORDER BY IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL Note that the Gloves table is created in the first SELECT statement. 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. IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables. SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours The following example uses the MERGE UNION query hint. SELECT ProductID, OrderQty, SUM(LineTotal) AS Total The following example shows how the OPTION (GROUP) clause is used with a GROUP BY clause. ON e.BusinessEntityID = pp.BusinessEntityID SELECT pp.LastName, pp.FirstName, e.JobTitleįROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Person AS pp JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID SELECT pp.FirstName, pp.LastName, e.NationalIDNumberįROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))
OSQUERY EXAMPLE SELECT HOW TO
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. The following example shows two ways to use the INDEX optimizer hint. SELECT ProductID, SUM(LineTotal) AS Total The query looks like this: USE AdventureWorks2012 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. SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total 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. It also organizes the results by ProductID. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. The following example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. GROUP BY SalesOrderID, CarrierTrackingNumber SELECT SalesOrderID, CarrierTrackingNumber This query uses the LIKE clause in the HAVING clause. The second example shows a HAVING clause without aggregate functions. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less. The first example that follows shows a HAVING clause with an aggregate function. The following example finds the average price of each type of product and orders the results by average price. NonDiscountSales = (OrderQty * UnitPrice) You can group by an expression if the expression does not include aggregate functions. The following example groups by an expression. SELECT ProductModelID, AVG(ListPrice) AS The following example puts the results into groups after retrieving only the rows with list prices greater than $1000. SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS , The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID. SELECT SalesOrderID, SUM(LineTotal) AS SubTotalīecause of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order. The following example finds the total of each sales order in the database. ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN SELECT DISTINCT pp.LastName, pp.FirstNameįROM Person.Person pp JOIN HumanResources.Employee e This example uses two correlated subqueries to find the names of employees who have sold a particular product. WHERE p1.ProductModelID = p2.ProductModelID) 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. SELECT Name, ProductNumber, ListPrice AS Price 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.
OSQUERY EXAMPLE SELECT CODE
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. The following example shows three code examples. Using SELECT to retrieve rows and columns
This topic provides examples of using the SELECT statement. Applies to: SQL Server (all supported versions) Azure SQL Database
0 kommentar(er)
