Returning Top Values in Font

Encode Code 3 of 9 in Font Returning Top Values

It is often easier to write queries that return more rows than you actually need to answer a question about the contents of a database. Sometimes you may actually want all the rows, but you only need to review the first few to verify that a query is performing properly. In these kinds of scenarios, adding the TOP keyword in a SELECT statement can return a subset of rows from the original result set, which is either a fixed number or a fixed percentage of rows from the top of the original set of rows. The samples for this section reside in ReturningTopValues.sql.

Note One innovation for the TOP keyword introduced with SQL Server 2005 (including SQL Server Express) is the ability to dynamically set the number or percent of rows that the SELECT statement returns. The first sample demonstrating the TOP keyword forces the return of just five rows from the ProductSubcategory table. You may recall this sample as nearly the same as an earlier one describing how to specify a list of items for a SELECT statement. I showed only the first five rows of the results from that earlier sample for reasons of brevity, but the following sample explicitly specifies the return of just five rows.

SELECT TOP 5 ProductSubcategoryID, ProductCategoryID, Name 'Subcategory name' FROM Production.ProductSubcategory

As mentioned, you can dynamically set the number of rows returned. The following code sample declares a local variable, @n, and then assigns a value of 6 to it. By enclosing the local variable s name in parentheses after the TOP keyword, you can have a SELECT statement return the number of rows assigned to the local variable s value. Changing the value of the local variable modifies the number of rows that return from the SELECT statement with the TOP keyword. DECLARE @n int SET @n = 6 SELECT TOP (@n) ProductSubcategoryID, ProductCategoryID, Name 'Subcategory name' FROM Production.ProductSubcategory By adding the PERCENT keyword after the fixed or variable value, you instruct SQL Server Express to return a percentage instead of an absolute number of rows. In other words, the PERCENT keyword causes TOP to interpret its argument as a percentage of rows. Therefore, SELECT TOP 10 PERCENT list FROM data source designates the return of the top 10% of rows from the data source.

By inserting TOP 100 PERCENT into a SELECT statement, you instruct SQL Server Express to return all the Tip rows from the original SELECT statement that did not use the TOP keyword. The TOP 100 PERCENT phrase is especially convenient with views that require the TOP keyword in order to sort a result set with the ORDER BY clause. 7 presents details on how to define views.

The precise number of rows returned with the PERCENT keyword depends on both the value specified as an argument for the TOP keyword as well as the number of rows in the original result set without the PERCENT keyword. A percent of a fixed number of rows will often result in a fractional number of rows, such as 4.07 rows. SQL Server Express returns rows to the nearest whole number above the computed number of rows. The following script shows the syntax for designating the return of a variable percent of rows. The assignment of 11 to @n specifies 11% of the rows. Without the TOP keyword, the SELECT statement returns 37 rows. 11% of 37 rows is 4.07 rows. Therefore, the statement returns 5 rows, which is the nearest whole number of rows above 4.07 rows. Assigning values of 10 and 9 to the @n local variable computes raw row values of 3.7 and 3.33. Because of rounding up to the nearest whole number, both values (10 and 9) for the TOP keyword return 4 rows. Assigning 8 to the @n local variable computes the return of 2.96 rows. Therefore, specifying the return of 8% leads to a result set with 3 rows. DECLARE @n int SET @n = 11 SELECT TOP (@n) PERCENT ProductSubcategoryID, ProductCategoryID, Name 'Subcategory name' FROM Production.ProductSubcategory