SQL TOP Statement

Introduction

 
In this chapter, we will learn about SQL TOP Statement and how TOP Statement is used with different options in SQL.
 

SQL TOP statement

 
The SQL TOP statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a fixed value or percentage.
 
The SQL  TOP statement limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server.
 
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 #ff0000 order.
 
SQL TOP statement uses 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
  1. SELECT TOP (top_value) [ PERCENT ]      
  2. expressions      
  3. FROM tables      
  4. WHERE conditions]      
  5. ORDER BY expression [ ASC | DESC ]];  

Using TOP statement  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 #ff0000 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 OrderName.
 
Syntax
  1. USE sample ;              
  2. GO              
  3. -- Select the first 10 random employees.              
  4. SELECT TOP(10)EmpName            
  5. FROM EmployeeDetail;              
  6. GO              
  7.  --Select the first 10 order Orderdetails most recently.              
  8. SELECT TOP(10) OrderName, orderAddress, OrderDate              
  9. FROM OrderDetails             
  10. ORDER BY OrderId DESC;              
  11. GO 
Example 
 

Using TOP with a variable in SQL

 
The following example uses a variable to specify the number of orderdetails that are returned in the query result set. 
 
Syntax 
  1. USE sample ;        
  2. GO        
  3. DECLARE @p AS int = 10;        
  4. SELECT TOP(@p) OrderName, orderAddress, OrderDate        
  5. FROM OrderDetails        
  6. ORDER BY OrderId DESC;        
  7. GO       
Example  
 
 

Using the Specifying a percentage

 
The following example uses a percentage to specify the orderName, OrderAddress of OrderDetails that are returned in the query result set. There are 10 ordeName, orderAddress in the OrderDetails table because five percent of 10 is a fractional value, the value is rounded up to the next whole number.
 
Syntax 
  1. USE sample ;        
  2. GO        
  3. SELECT TOP(5)PERCENT OrderName, orderAddress        
  4. FROM OrderDetails        
  5. ORDER BY OrderId DESC;        
  6. GO    
Example
 
 

Using with Ties to include rows that match the values in the last row

 
The following example gets the top 10 percent of all OrderDetails with the highest and returns them in descending order according to their orderderails. Specifying with Ties ensures that OrderName is with OrderDetails.
 
Syntax
  1. USE sample      
  2. GO        
  3. SELECT TOP(10) PERCENT WITH TIES   pp.OrderName, e.EmpName, e.EmpAddress        
  4. FROM OrderDetails AS pp         
  5.     INNER JOIN  EmployeeDetail AS e        
  6.         ON pp.OrderId = e.EmpId       
  7.     INNER JOIN EmployeeDetails  class="keyword">AS r        
  8.         ON r.EmployeeID = e.EmpId        
  9. ORDER BY OrderId  DESC;        
  10. GO 
Example 
 

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 OrderDetails table that have due dates earlier than July 1, 2019.
 
Syntax 
  1. USE sample ;        
  2. GO        
  3. DELETE TOP (20)         
  4. FROM OrderDetails        
  5. WHERE OrderDate < '20190701';        
  6. GO   
     
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  OrderDetails table that have the earliest due dates.
 
To ensure that only 10 rows are deleted, the column specified in the subselect statement (OrderId) is the primary key of the table. Using a non-key column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.
 
Syntax
  1. USE sample;        
  2. GO        
  3. DELETE FROM OrderDetails         
  4. WHERE OrderId  IN        
  5.    (SELECT TOP 10 EmployeeID         
  6.     FROM EmployeeDetails       
  7.     ORDER BY OrderDate class="keyword">ASC);        
  8. GO  
     

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 EmployeeSales. The INSERT  statement chooses any five rows returned by the SELECT statement that meets 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.
 
Syntax  
  1. USE sample       
  2. GO        
  3. IF OBJECT_ID ('OrderDetails', 'U') IS NOT NULL        
  4.     DROP TABLE dbo.EmployeeSales;        
  5. GO        
  6. CREATE TABLE dbo.EmployeeSales        
  7. ( EmployeeID   nvarchar(11) NOT NULL,        
  8.   LastName     nvarchar(20) NOT NULL,        
  9.   FirstName    nvarchar(20) NOT NULL,        
  10.   YearlySales  money NOT NULL        
  11.  );        
  12. GO        
  13. INSERT TOP(5)INTO dbo.EmployeeSales values(1,'Gupta','Singh',2008)      
  14.     SELECT sp.OrderId, sp.OrderName,sp.orderAddress         
  15.     FROM OrderDetails AS sp        
  16.     INNER JOIN EmployeeDetail  AS c        
  17.         ON sp.OrderId = c.EmpId        
  18.     WHERE sp.OrderId > 250000.00        
  19.     ORDER BY sp.OrderId DESC;        
  20. GO   
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 shows how to do this.
 
The output clause displays the rows that are inserted into the OrderDetails table. Notice that the top five employees are now inserted based on the results of the ORDER BY clause instead of undefined rows.
 
Syntax
  1. SELECT TOP(10) PERCENT WITH TIES   pp.OrderName, e.EmpName, e.EmpAddress        
  2. FROM OrderDetails AS pp         
  3.     INNER JOIN  EmployeeDetail AS e        
  4.         ON pp.OrderId = e.EmpId       
  5.     INNER JOIN EmployeeDetails AS r        
  6.         ON r.EmployeeID = e.EmpId        
  7. ORDER BY OrderId  DESC;        
  8. GO     

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 OrderDetails from OrderId to another.
 
Syntax 
  1. USE sample ;        
  2. UPDATE TOP (10) OrderDetails        
  3. SET OrderId  = 31       
  4. WHERE OrderId = 2;        
  5. GO  
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 orderId 10 orderDetails with the orderId.
 
Syntax
  1. UPDATE OrderDetails        
  2. SET orderAddress = OrderId          
  3. FROM (SELECT TOP 10 OrderName FROM OrderDetails          
  4.      ORDER BY OrderDate ASC) AS th          
  5. WHERE OrderId = OrderId;          
  6. GO    

Summary

 
In the next chapter, we will learn how to use a SQL BETWEEN statement with various options.
Author
Naresh Beniwal
Editor 7.1k 1.6m
Next » SQL BETWEEN Statement

©2024 C# Corner.