Selecting Top N Number of Rows - SQL Server 2005

Hi all,

We can get rows from a particular table by using the Select query as follows,

select * from [Table_Name]

If we need 10 rows, we can use Top query like as follows,

select top 10 * from [Table_Name]

Suppose we need to pass the number of rows count as parameter…How to do?

Yes, the way of using ROW_NUMBER(), we can do that. For more, see the following example,

First we are going to create a table namely TestCount.

create table TestCount(SNo int, Name varchar(200))

Insert values in to that table.

insert into TestCount(SNo,Name) values(1,'aaa')
insert into TestCount(SNo,Name) values(2,'bbb')
insert into TestCount(SNo,Name) values(3,'ccc')
insert into TestCount(SNo,Name) values(4,'ddd')
insert into TestCount(SNo,Name) values(5,'eee')
insert into TestCount(SNo,Name) values(6,'fff')
insert into TestCount(SNo,Name) values(7,'ggg')
insert into TestCount(SNo,Name) values(8,'hhh')
insert into TestCount(SNo,Name) values(9,'iii')
insert into TestCount(SNo,Name) values(10,'jjj')

Now use the following query to get top n number of rows.

declare @Count int
set @Count = 5
SELECT SNo,Name FROM
(select ROW_NUMBER() over (order by [SNo])
as Row, * FROM
(SELECT SNo,Name FROM TestCount)
as userquery)
as
SelectionWithRowNumbers WHERE Row >0 AND Row<=@Count

That’s it. Hence we can get top n number of rows from a table.

…S.VinothkumaR.