Filtering Data With TOP Option in SQL

Introduction

We can filter the top N rows or percent of rows from the query result based on an order by.

When we use the Top Option, then Order by is not required. In SQL, the Offset-fetch functionwasn introduced. For more information, see offset–fetch.

1. Top (N) [With Ties]. This means the number of rows; N is the constant value or @variable (BIGINT) data type.
2. Top (N) [With Ties] – Percent. This means the percentage of rows is based on value.

Example

 CREATE TABLE #STUDENT  
(  
NAME VARCHAR (50),  
MARKS INT  
)  
  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAKESH', 99)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('MADHU', 95)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('NARESH', 88)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAJU', 87)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('PANKAJ', 95)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAMU', 66)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('SHEKAR', 90)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAKI', 68)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('MAHESH', 95)  
INSERT INTO #STUDENT (NAME, MARKS) VALUES ('PARVEEN', 94)  
  
  
SELECT * FROM #STUDENT

Top (N)

Top 2 Students from Student table based on Marks.

The solution to the preceding is given here:

SELECT TOP 2 WITH TIES * FROM #STUDENT  
ORDER BY MARKS DESC 

Top (N) Percent

Using Top with PERCENT will give the percent of rows.

Example

SELECT TOP 50 PERCENT * FROM #STUDENT  
ORDER BY MARKS DESC 

We have 10 rows in the Student table. Out of that, we have selected 50 percent of the rows from the Student table. The result of rows will be 5.

Top (N) Percent with Ties

SELECT TOP 50 PERCENT WITH TIES * FROM #STUDENT  
ORDER BY MARKS DESC