In focus

How To Use ROW_NUMBER() function

In this article we will learn how to use ROW_NUMBER() function and doing filter work by using it.

Gagan Sharma Apr 14, 2016

Sometimes we need database side paging on largest data. Client side paging techniques are insufficient in this case. For this we use ROW_NUMBER() function.

ROW_NUMBER() method is use with the number of a row in a given recordset. ROW_NUMBER function selects scalar value for all rows that is displayed by ordered column.
 
ROW_NUMBER() method helps in limiting the number of rows returned from a query, also it assigns a unique number to each row to which it is applied.

Syntax: ROW_NUMBER() OVER ()

We can perform logical paging to display results based on the requirement on selected values to be displayed. We must use this technique over ROW_NUMBER valued column with BETWEEN statement. Sub query for using filtering row_number valued column is use.
  
We can select this value with any query like this:  Let us suppose we have a existing table.

Query: 
  1. Select * from person.person  
Output:

11

Use of ROW_NUMBER() method:

Query:  Select row_number() over (order by BusinessEntityId ) as RowNumber , * from person.person

Output: From our table, the rows are now sorted from 1 to 19972 in ascending order based on column name "BusinessEntityId ".

22

Now we will learn how to use ROW_NUMBER() function and doing filter work (logical paging) with the us of BETWEEN clause.

Query:
  1. SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY BusinessEntityId) AS RowNumber,  * FROM person.person ) as z WHERE RowNumber BETWEEN 910 AND 13400  
Output: We can view the selected rows between 910 to 13400.

clip

function scalar select

COMMENT USING