SQL-TOP Keyword

SQL TOP Statement

The SQL TOP statement is used to return number of records from one or more tables in a database based on a fixed value or percentage. The TOP statement is useful on large tables with thousands of records. Returning a large number of records can impact performance.

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.

Sometimes we want to retrieve or return a specific number of records from the SQL table rather than retrieving all table records. We can use the WHERE clause in a SELECT statement to limit the number of rows. Suppose you have a students table and you want to retrieve records for students belonging to a particular school. There might be many records satisfying the condition. We require top N student records to satisfy the conditions. For this requirement, we can also use TOP or ROW_Number() clauses.

Syntax

SELECT TOP (expression) [ PERCENT ] [WITH TIES]
FROM tables 
WHERE conditions] 
ORDER BY expression [ ASC | DESC ];

The syntax Parameters or Arguments are defined as following:

Expression

Following the TOP keyword is an expression that specifies the number of rows to be returned. For example, TOP(10) would return the top 10 rows from the full result set. The expression is evaluated to a float value if PERCENT is used, otherwise, it is converted to a BIGINT value.

PERCENT

The PERCENT keyword indicates that the query returns the first N percentage of rows, where N is the result of the expression. For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

The WITH TIES allows you to return more rows with values that match the last row in the limited result set. Note that WITH TIES may cause more rows to be returned than you specify in the expression.

1. TOP Clause with a Constant Value

In this example, SQL Table have total 6 record using top we retrieve the top 4 records from that table without specifying any condition and ORDER BY Clause. In the next query, We can specify the required column names in the select statement. In the next query, we specify an ORDER BY Clause to sort results in descending order of StudentName column.

The Query looks like that:

Select count(StudentID) as TotalRecord from students
Go 
Select top(4) * from students
GO
Select top(4) StudentID, StudentName, marks from students
Go
Select top(5) StudentID, StudentName, marks from students order by StudentName desc

Output

2. TOP Clause with a PERCENT value

The PERCENT keyword indicates that the query returns the first N percentage of rows, where N is the result of the expression. For example, TOP(10) PERCENT would return the top 10% of the full result set. The following query returns 50 percent of rows in the output.

We have a total of 6 records in the in student table. 50% of total record,

(6*50/100) = 3 Records

So Query will return 3 records.

The Query looks like that:

Select count(StudentID) as TotalRecord from students
Go 
Select 
top(50) percent * from students
GO
Select top(50)percent StudentID, 
StudentName, marks from students

Output

3. TOP Clause and WITH TIES

The following example gets the top 2 percent of all OrderDetails with the highest and returns them in descending order according to their orderderails. The WITH TIES allows you to return more rows with values that match the last row in the limited result set. Note that WITH TIES may cause more rows to be returned than you specify in the expression.

The Query looks like that:

Select count(StudentID) as TotalRecord from students
Go
Select top(2) with ties StudentID, StudentName, marks from students order by StudentID desc

Output

4. TOP Clause and Delete

The following example deletes 5 rows from the student table. That have studentid less than 5.

The Query looks like that:

DELETE TOP (5) FROM students 
WHERE StudentID < 5;

5. TOP Clause and Insert

The following example insert 2 rows from the student table in studentscopy table.

The Query looks like that:

DELETE TOP (5) 
FROM students 
WHERE StudentID < 5;

Insert into studentscopy(StudentID, StudentName, marks)
Select top(2) with ties StudentID, StudentName, marks from students order by StudentID desc

6. TOP Clause and Update

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.

Example

UPDATE TOP (5) students
SET studentid = 10 
WHERE studentid = 2; 

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.

Example

UPDATE students
SET marks = studentid
FROM (SELECT TOP 10 studentname FROM students
ORDER BY studentid ASC) AS th 
WHERE studentid = studentid; 
GO