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.

2 comments:

murali said...
This comment has been removed by the author.
murali said...

To select the top n number of rows in a record we can use the below query also
Eg:
select top n * from table1;

where n-- represents the numbers row to be fecthed from the top of the table1.

for instance:

select top 5 * from table1;

the above eg fetches only top 5 records from the 'table1'