Using Top clause for Selecting, Inserting, Updating and deleting records in SQL Server

Hello friendsJ,

 

Most of us might have worked with TOP clause very frequently to get only the specific first set of rows but I have never used it with the combination of INSERT or DELETE or UPDATE.

 

So I just want to share what I have learned today and I hope you find it usefulJ

 

TOP clause specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. It can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Syntax:-

 

[

     TOP (expression) [PERCENT]

     [WITH TIES]

]

Arguments:-

Expression

Is the numeric expression that specifies the number of rows to be returned. Expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Parentheses that delimit expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT

Indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES

Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP …WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example:- Click here to download the script

CREATE TABLE Dept( Dept_id INT PRIMARY KEY, DeptName VARCHAR(100), Loc VARCHAR(100))

GO

 

INSERT INTO Dept VALUES(101,‘Admin’,‘Chennai’)

INSERT INTO Dept VALUES(102,‘Sales’,‘Hyderabad’)

INSERT INTO Dept VALUES(103,‘Operations’,‘Delhi’)

INSERT INTO Dept VALUES(104,‘IT’,‘Banglore’)

 

GO

CREATE TABLE Employee(Emp_id INT PRIMARY KEY, EmpName VARCHAR(100),DOJ DATETIME, Salary MONEY, Dept_id INT REFERENCES Dept(Dept_id))

 

GO

INSERT INTO Employee VALUES(1001,‘Chandra’,’15-Dec-2004′,10000,104)

INSERT INTO Employee VALUES(1002,‘VVS’,’01-Jan-2005′,30000,104)

INSERT INTO Employee VALUES(1003,‘Pagalavan’,’15-Oct-2005′,40000,104)

INSERT INTO Employee VALUES(1004,‘ASN’,’10-Feb-2006′,20000,104)

INSERT INTO Employee VALUES(1005,‘Sathish’,’19-Dec-2005′,10000,104)

INSERT INTO Employee VALUES(1006,‘Sri’,’06-Sep-2007′,50000,104)

INSERT INTO Employee VALUES(1007,‘Sangeetha’,’10-Nov-2007′,15000,104)

INSERT INTO Employee VALUES(1008,‘Manick’,’25-Jan-2006′,20000,104)

INSERT INTO Employee VALUES(1009,‘Kanchana’,’15-Feb-2005′,19000,104)

INSERT INTO Employee VALUES(1010,‘Arun’,’15-Mar-2005′,12000,103)

INSERT INTO Employee VALUES(1011,‘Zahed’,’05-Apr-2004′,11000,103)

INSERT INTO Employee VALUES(1012,‘Rajesh’,’14-May-2005′,60000,103)

INSERT INTO Employee VALUES(1013,‘Ila’,’15-Jun-2006′,80000,102)

INSERT INTO Employee VALUES(1014,‘Remya’,’15-Jul-2006′,40000,102)

INSERT INTO Employee VALUES(1015,‘Tito’,’15-Aug-2008′,70000,102)

INSERT INTO Employee VALUES(1016,‘RP’,’11-Sep-2007′,30000,102)

INSERT INTO Employee VALUES(1017,‘Khiroj’,’18-Oct-2008′,40000,102)

INSERT INTO Employee VALUES(1018,‘Gopi’,’20-Nov-2005′,60000,101)

INSERT INTO Employee VALUES(1019,‘Venkat’,’12-Dec-2007′,10000,101)

INSERT INTO Employee VALUES(1020,‘JJR’,’12-Jan-2006′,15000,101)

INSERT INTO Employee VALUES(1021,‘Shivan’,’15-Feb-2008′,25000,101)

GO

 

Using TOP with variables

 

          DECLARE @P INT

          SET @P = 10

          SELECT TOP (@P) * FROM Employee

 

Using TOP with PERCENT and WITH TIES

 

The following example obtains the top 10 percent of all employees with the highest salary and returns them in descending order according to salary base rate. Specifying WITH TIES makes sure that any employees with salaries equal to the lowest salary returned are also included in the result set, even if doing this exceeds 10 percent of employees.

 

Excluding “WITH TIES”

 

Select TOP (10) PERCENT

Emp_id,Empname,Salary

From Employee

order by salary desc

 

Output:-

Emp_id	Empname	Salary
1013	Ila	80000
1015	Tito	70000
1012	Rajesh	60000

 

Including “WITH TIES” – It returns extra rows which matches the least salary in the above output

 

Select TOP (10) PERCENT WITH TIES

Emp_id,Empname,Salary

From Employee

order by salary desc

 

Output:-

Emp_id	Empname	Salary
1013	Ila	80000
1015	Tito	70000
1012	Rajesh	60000
1018	Gopi	60000

 

 

Using TOP with INSERT

 

SELECT * INTO #x FROM Employee Where Emp_id = -1

 

/*The previous query creates a temp table with the structure of Employee table*/

 

INSERT TOP (2) INTO #x

     SELECT * FROM Employee

     ORDER BY Salary desc

Go

Select * From #x

 

Output:-

Emp_id	EmpName	DOJ	Salary	Dept_id
1001	Chandra	00:00.0	10000	104
1002	VVS	00:00.0	30000	104

 

The ORDER BY clause in the previous query references only the rows returned by the nested SELECT statement. The INSERT statement chooses any two rows returned by the SELECT statement by default in the sorting order of primary key column and it doesn’t consider the order by column given in the SELECT statement.

 

To make sure that the top two rows from the SELECT sub query are inserted, rewrite the query as follows.

 

INSERT  INTO #x

     SELECT TOP (2) * FROM Employee

     ORDER BY Salary desc

Go

Select * From #x

 

Output:-

Emp_id	EmpName	DOJ	Salary	Dept_id
1013	Ila	00:00.0	80000	102
1015	Tito	00:00.0	70000	102

 

 

Using TOP with UPDATE

 

UPDATE TOP (2) Employee

SET Salary = 10000

 

Using TOP with DELETE

 

DELETE TOP (2) Employee

 

Source :- MSDN J J J