This tutorial shows you how to use the SQL Server SELECT TOP statement to limit the. As a result, the result set is limited to the first N number of ordered rows.
Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
It's more useful to update specific rows. Like with SELECT statements, you can use conditions to target specific rows.
SQL Used
An update statement for specific rows:
Examples:
Update multiple columns for specific rows:
Examples:
See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.
- 0:00Now it's one thing changingall entries in a table,
- 0:03how about targeting specific rows?
- 0:06I can see that being more useful.
- 0:09Let's start with our original UPDATEstatement that updates all the rows.
- 0:13How do you think we should selectthe rows where we want to update them?
- 0:17Well, just like a SELECT statementwhere you can use a WHERE clause,
- 0:21you can do the same thing with WHEREconditions on UPDATE statements.
- 0:26This time, instead of reading the rows,
- 0:28you're updating the rowsthat match this criteria.
- 0:32Remember, conditions comein all shapes and sizes.
- 0:36Some have operators such as a quality andinequality, greater than or
- 0:40less than to name a few.
- 0:42Then there's searching in setsof values with the IN keyword.
- 0:46There's also conditionswhere you can search for
- 0:48ranges with the BETWEEN keyword.
- 0:51Finally, you could use the LIKEkeyword where you could search for
- 0:55patterns of characters.
- 0:57If you've taken the prerequisites orhave the prerequisite knowledge for
- 1:01this course, you should befamiliar with these conditions.
- 1:04Let's use some of theseconditions to update rows.
- 1:08Let's look for some missinginformation in our books table.
- 1:14As you can see in the last entry,
- 1:16Animal Farm, there’s a missingvalue in the genre column.
- 1:20What condition would we use inorder just to select this row?
- 1:24The most unique valuewould be the ID of 20.
- 1:37Other books could have the name,Animal Farm, or the same author,
- 1:42or released in 1945.
- 1:44Let's update the genre ofAnimal Farm to Classic.
- 1:58All right, let's take anotherlook at Animal Farm again.
- 2:13It's now a Classic.
- 2:15Let's update our loans table too.
- 2:18Let's take a look at it andsee what values we're missing.
- 2:22Let's say the patronwith the ID of 1 returned
- 2:25all their books that they've loaned,books 4 and 8.
- 2:29Let's imagine today is December 18th,2015.
- 2:35So let's start with UPDATE loans
- 2:40SET returned_on =
- 2:44'2015-12-18'.
- 2:50Now to the WHERE clause.
- 2:54Well I could do something like this,WHERE patron_id = 1,
- 3:00and this will update the rows we need.
- 3:04But imagine there were hundreds orthousands of rows,
- 3:08we may not want to updateall the loans for patron 1.
- 3:12They may have loaned hundredsof books over the years, so
- 3:16to qualify it further would be better.
- 3:19I could do, AND,
- 3:27returned_on IS NULL.
- 3:34This will only update loansthat hadn't been returned for
- 3:37patron 1, and not any historical ones.
- 3:40Now you can go even further and
- 3:47add, AND book_id IN (4, 8).
- 3:54Just in case if they had rentedout a different book and
- 3:58they hadn't returned that one yet.
- 4:00But since we can see allentries in the loans table and
- 4:05all the entries for patron 1,we can simply do, WHERE patron id = 1
- 4:15And as you can seethe returned_on has been updated.
- 4:19Depending on your circumstances,
- 4:21you can update multiple rows basedon any condition you construct.
- 4:25The WHERE conditions are exactly howyou'd use them in a SELECT statement.
- 4:30Instead of retrieving rows,you're updating them.
- 4:33So before running the UPDATE statement,you can try your condition out first with
- 4:37a SELECT query to make sure that the rowsreturned are the ones you want to update.
You need to sign up for Treehouse in order to download course files.
-->Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2019 (15.x). When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement. Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement.
Syntax
Following is the syntax for SQL Server and Azure SQL Database:
Following is syntax for Azure SQL Data Warehouse and Parallel Data Warehouse:
Arguments
expression
The numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if you specify PERCENT. Otherwise, expression is converted to bigint.
The numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if you specify PERCENT. Otherwise, expression is converted to bigint.
PERCENT
Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.
Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.
WITH TIES
Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.
Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.
You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. The returned order of tying records is arbitrary. ORDER BY doesn't affect this rule.
Best Practices
In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.
Use OFFSET and FETCH in the ORDER BY clause instead of the TOP clause to implement a query paging solution. A paging solution (that is, sending chunks or 'pages' of data to the client) is easier to implement using OFFSET and FETCH clauses. For more information, see ORDER BY Clause (Transact-SQL).
Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. These methods are preferred over using SET ROWCOUNT for the following reasons:
- As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because you use SET ROWCOUNT outside of a statement that runs a query, its value can't be considered in a query plan.
Compatibility Support
![Sql Sql](/uploads/1/2/5/6/125663394/925610404.png)
For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements. Doing so provides consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements.
Interoperability
The TOP expression doesn't affect statements that might run because of a trigger. The inserted and deleted tables in the triggers return only the rows that are truly affected by the INSERT, UPDATE, MERGE, or DELETE statements. For example, if an INSERT TRIGGER fires as the result of an INSERT statement that used a TOP clause.
SQL Server allows for updating rows through views. Because you can include the TOP clause in the view definition, certain rows may disappear from the view if the rows no longer meet the requirements of the TOP expression due to an update.
When specified in the MERGE statement, the TOP clause applies after the entire source table and the entire target table are joined. And, the joined rows that don't qualify for an insert, update, or delete action are removed. The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions apply to the remaining joined rows in an unordered way. That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. For example, if specifying TOP (10) affects 10 rows, of these rows, seven may be updated and three inserted. Or, one may be deleted, five updated, and four inserted, and so on. Because the MERGE statement does a full table scan of both the source and target tables, I/O performance can be affected when you use the TOP clause to modify a large table by creating multiple batches. In this scenario, it's important to ensure that all successive batches target new rows.
Use caution when you're specifying the TOP clause in a query that contains a UNION, UNION ALL, EXCEPT, or INTERSECT operator. It's possible to write a query that returns unexpected results because the order in which the TOP and ORDER BY clauses are logically processed isn't always intuitive when these operators are used in a select operation. For example, given the following table and data, assume that you want to return the least expensive red car and the least expensive blue car. That is, the red sedan and the blue van.
To achieve these results, you might write the following query.
Following is the result set.
The unexpected results are returned because the TOP clause logically runs before the ORDER BY clause, which sorts the results of the operator (UNION ALL in this case). So, the previous query returns any one red car and any one blue car and then orders the result of that union by the price. The following example shows the correct method of writing this query to achieve the desired result.
By using TOP and ORDER BY in a subselect operation, you ensure that the results of the ORDER BY clause are applied to the TOP clause and not to sorting the result of the UNION operation.
Here is the result set.
Limitations and Restrictions
![Statements Statements](/uploads/1/2/5/6/125663394/897627388.png)
When you use TOP with INSERT, UPDATE, MERGE, or DELETE, the referenced rows aren't arranged in any order. And, you can't directly specify the ORDER BY clause in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, use TOP with an ORDER BY clause specified in a subselect statement. See the following Examples section in this article.
You can't use TOP in an UPDATE and DELETE statements on partitioned views.
You can't combine TOP with OFFSET and FETCH in the same query expression (in the same query scope). For more information, see ORDER BY Clause (Transact-SQL).
Examples
Category | Featured syntax elements |
---|---|
Basic syntax | TOP • PERCENT |
Including tie values | WITH TIES |
Limiting the rows affected by DELETE, INSERT, or UPDATE | DELETE • INSERT • UPDATE |
Basic syntax
Examples in this section demonstrate the basic functionality of the ORDER BY clause using the minimum required syntax.
A. Using TOP with a constant value
The following examples use a constant value to specify the number of employees that are returned in the query result set. In the first example, the first 10 undefined rows are returned because an ORDER BY clause isn't used. In the second example, an ORDER BY clause is used to return the top 10 recently hired employees.
B. Using TOP with a variable
The following example uses a variable to specify the number of employees that are returned in the query result set.
C. Specifying a percentage
The following example uses PERCENT to specify the number of employees that are returned in the query result set. There are 290 employees in the
HumanResources.Employee
table. Because five percent of 290 is a fractional value, the value is rounded up to the next whole number.Including tie values
A. Using WITH TIES to include rows that match the values in the last row
The following example gets the top
10
percent of all employees with the highest salary and returns them in descending order according to their salary. Specifying WITH TIES
ensures that employees with salaries equal to the lowest salary returned (the last row) are also included in the result set, even if it exceeds 10
percent of employees.Limiting the rows affected by DELETE, INSERT, or UPDATE
A. Using TOP to limit the number of rows deleted
When you use a TOP (n) clause with DELETE, the delete operation is done on an undefined selection of n number of rows. That is, the DELETE statement chooses any (n) number of rows that meet the criteria defined in the WHERE clause. The following example deletes
20
rows from the PurchaseOrderDetail
table that have due dates earlier than July 1, 2002.If you want to use TOP to delete rows in a meaningful chronological order, use TOP with ORDER BY in a subselect statement. The following query deletes the 10 rows of the
PurchaseOrderDetail
table that have the earliest due dates. To ensure that only 10 rows are deleted, the column specified in the subselect statement (PurchaseOrderID
) is the primary key of the table. Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.B. Using TOP to limit the number of rows inserted
The following example creates the table
EmployeeSales
and inserts the name and year-to-date sales data for the top five employees from the table HumanResources.Employee
. The INSERT statement chooses any five rows returned by the SELECT
statement that meet the criteria defined in the WHERE clause. The OUTPUT clause displays the rows that are inserted into the EmployeeSales
table. Notice that the ORDER BY clause in the SELECT statement isn't used to determine the top five employees.If you want to use TOP to insert rows in a meaningful chronological order, use TOP with ORDER BY in a subselect statement. The following example show how to do this. The OUTPUT clause displays the rows that are inserted into the
EmployeeSales
table. Notice that the top five employees are now inserted based on the results of the ORDER BY clause instead of undefined rows.C. Using TOP to limit the number of rows updated
The following example uses the TOP clause to update rows in a table. When you use a TOP (n) clause with UPDATE, the update operation runs on an undefined number of rows. That is, the UPDATE statement chooses any (n) number of rows that meet the criteria defined in the WHERE clause. The following example assigns 10 customers from one salesperson to another.
If you have to use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. The following example updates the vacation hours of the 10 employees with the earliest hire dates.
Examples: Azure Synapse Analytics (SQL DW) and Parallel Data Warehouse
The following example returns the top 31 rows that match the query criteria. The ORDER BY clause ensures that the 31 returned rows are the first 31 rows based on an alphabetical ordering of the
LastName
column.Using TOP without specifying ties.
Result: 31 rows are returned.
Using TOP, specifying WITH TIES.
Result: 33 rows are returned, because three employees named Brown tie for the 31st row.
See Also
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
ORDER BY Clause (Transact-SQL)
SET ROWCOUNT (Transact-SQL)
MERGE (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
ORDER BY Clause (Transact-SQL)
SET ROWCOUNT (Transact-SQL)
MERGE (Transact-SQL)