Filtering Data With TOP Option in SQL

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

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

1. Top (N) [With Ties]: Means the number of rows, here N is constant value or @variable (BIGINT) data type.
   
   
2. Top (N) [With Ties] – Percent: Means the percent of rows based on value.

Example

1.  CREATE TABLE #STUDENT  
2. (  
3. NAME VARCHAR (50),  
4. MARKS INT  
5. )  
6.   
7. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAKESH', 99)  
8. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('MADHU', 95)  
9. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('NARESH', 88)  
10. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAJU', 87)  
11. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('PANKAJ', 95)  
12. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAMU', 66)  
13. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('SHEKAR', 90)  
14. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('RAKI', 68)  
15. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('MAHESH', 95)  
16. INSERT INTO #STUDENT (NAME, MARKS) VALUES ('PARVEEN', 94)  
17.   
18.   
19. SELECT * FROM #STUDENT

Top (N)
Top 2 Students from Student table based on Marks.





The solution of the preceding is given here:

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

Top (N) Percent
Using Top with PERCENT will give the percent of rows.

Example

1. SELECT TOP 50 PERCENT * FROM #STUDENT  
2. 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

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