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

Advertisements

One response to “Limit Record Number on SQL Server 2005 using Row_number() function

  1. Pingback: Optimize Large Recordset Custom Paging using ObjectDataSource, ListView, DataPager ASP .Net Controls and Row_number() SQL Server function | Enlighten Application Developer Journals

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s