Limit Record Number on SQL Server 2005 using Row_number() function

SQL Server has capability to show sequence record number on ‘select’ statement. This also referred as record ranking. The function is Row_number() over (order by <columnname>).
The order of sequence relate to specific column name using Order by columnname part. The order can be ascending or descending.

I use AdventureWorks on SQL Server 2005 for demonstration purpose. The sql code will show Store Type Customer info like Store Name, Territory and Sales Value.
Here’s the sql code:

select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name order by Sales.Store.Name

It declare Rowno column as a sequence record number and its order is ascending Store Name.

Try with descending order of Store Name:

select row_number() over(order by Sales.Store.Name desc) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name
order by StoreName

We will see the sequence order is descending.

So how to limit record with row_number() function? We can use above sql code and declare it as a table and use that table to filter using rowno column.
For example we want to show first record until 50th use below code:

select * from (
select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name
) as tbl where rowno between 1 and 50
order by StoreName

As you can see that above query is declared as tbl table and it use a where and between clause to filter rows.
Try with different parameters i.e between 51 and 100.

This sql code is usefull to break one large recordset into several recordsets. Retrieve large recordset consumes a lot of resources and time on web request. We can use this sql method to devide one large web page into pages.

Cheers,
Agung Gugiaji