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

 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 of 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