retrive specific number of records from table in sql server database

Using ROW_NUMBER() in sql, you can have unique record numbers in your query result.

it returns the sequential number of a row within a your query result set.

Syntax of it:

ROW_NUMBER () OVER ( [ ] )

here, order by clause, determines the order in which the ROW_NUMBER value is assigned to the rows

Following example demonstrates the use of it:


select * from
(
select p.keyid, p.name, p.addeddate, ROW_NUMBER() OVER (order by p.name) as [ROWID]
from products p
) as [mytmptable]
where ROWID between 2 and 5
ORDER BY ROWID ASC

Above technique become useful especially in case of  you need to retrieving limited number of records from your query result(like while implementing paging functionality).

Advertisements